I'm working in Excel to extract content of a table from a web page and populate Excel sheet cells with it. I'm trying to extract data from a table that gets loaded to a web page after show results method gets called. I have an error that says "Run-time error '424' Object required".

Sub extractDataFromTable()
    Dim IE As Object
    Dim theTable As Object
    Dim form As Variant, button As Variant
    Dim doc As Object
    Set IE = CreateObject("InternetExplorer.Application")

    ' navigate to a web page
    With IE
        .Visible = True
        .navigate ("http://www.sizemyups.com")
    End With

    While IE.ReadyState <> 4
        DoEvents
    Wend

    Set doc = IE.document
    ' prefill the data to the website
    doc.getElementsByName("load").Item.innertext = 50
    doc.getElementsByName("line").Item(0).Value = "Endeavor Series"
    ' make the table with data load
    Set form = doc.getElementsByName("line")
    Set button = form(0).onchange
    form(0).onchange
    ' set the data from the first tr tag
    Set theTable = doc.getElementById("mytable").getElementsByTagName("tr")
    ' extract the data from tag and assign it to the first cell in the sheet
    Dim myValue As String
    myValue = theTable(0).innertext
    Cells(1, 1).Value = "Cell content " & myValue
End Sub

If I change the Set theTable = doc.getElementById("mytable").getElementsByTagName("tr") to Set theTable = doc.getElementById("homepu").getElementsByTagName("tr"), I don't get any errors, and the code works as I want it. I've been trying to figure out what's wrong, but didn't get anywhere. What am I doing wrong?

Member Avatar for diafol

Can you show the relevant html from the webpage?

Member Avatar for diafol

The site I visited does not have a "mytable" id in any tag although it is referenced in the styling.
A "homepu" id is attached to a table (empty) - but strangely it is in the head section of the html document.
The webpage is horrible - js, html, styling all mishmashed to hell - yuck.

//EDIT

Well, "mytable" exists once the form is submitted - this requires a button click. Perhaps that's why you can't get it to work programmatically.

Here's the webpage html before my code runs:
HTML_code_before.png

And here's the webpage html after I ran my code:
HTML_code.png

Indeed. The button on the webpage appears as an image, and I don't know how to programmatically call a button click on it. I've done some debugging and the code that I assumed will make the table load with data, doesn't update the results as they should be.

I tried to get the button to be clicked by the following code:

    Dim Element, txt As Object
    Set Element = doc.getElementById("myform").getElementsByTagName("a")
    For Each txt In Element
        If txt.href = "javascript:onclick=showResults(document.myform.load.value, document.myform.AVW.value, document.myform.loadtype.value, document.myform.standby.value, document.myform.lineint.value, document.myform.online.value, document.myform.line.value, document.myform.involts.value, document.myform.outvolts.value, document.myform.rack.value, document.myform.sine.value, document.myform.min.value, document.myform.max.value, document.myform.growth.value,document.myform.sort.value,document.myform.uid.value,document.myform.htemp.value)" Then
            txt.Click
        End If
    Next

But this still didn't fix the issue.

Member Avatar for diafol

I get that now - see my previous post - however, you need to be able to programmatically send the form (or the data) in order to get at the table.

You may be better sending data directly via http://www.sizemyups.com/getups.php with a url querystring:

Try this:

http://www.sizemyups.com/getups.php?load=50&AVW=WATTS&loadtype=MSL&standby=1&lineint=1&online=1&line=&involts=120&outvolts=120&rack=0&sine=0&min=0&max=0&growth=0&sort=estrt&uid=1011&htemp=0

What you then get is a nice table (mytable) - so try that you can pull data as you wish. Here's an example:

Sub extractDataFromTable()
    Dim IE As Object
    Dim theTable As Object
    Dim form As Variant, button As Variant
    Dim doc As Object

    Dim t As Variant
    Dim j As Integer
    Dim k As Integer

    Set IE = CreateObject("InternetExplorer.Application")
    ' navigate to a web page
    With IE
        .Visible = True
        .navigate ("http://www.sizemyups.com/getups.php?load=50&AVW=WATTS&loadtype=MSL&standby=1&lineint=1&online=1&line=&involts=120&outvolts=120&rack=0&sine=0&min=0&max=0&growth=0&sort=estrt&uid=1011&htemp=0")
    End With
    While IE.ReadyState <> 4
        DoEvents
    Wend

    Set doc = IE.document

    Set theTable = doc.getElementById("mytable").getElementsByTagName("td")
    ' extract the data from tag and assign it to the first cell in the sheet
    Dim myValue As String

    j = 1
    k = 1

    For Each t In theTable
        myValue = t.innertext
        Cells(k, j).Value = myValue
        j = j + 1
        If j Mod 6 = 0 Then
            j = 1
            k = k + 1
        End If

    Next t

End Sub

BTW - I haven't touched VBA in over 10 years, so my code may be stupidly out of date! It seems to work for me - but may need tweaking.

Member Avatar for diafol

Not sure if this would be better:

.Visible = True   => remove it so it doesn't show?

Also you should set up variables for the querystring parameters in order to make this sub a little more flexible / usable.

Just a thought. Since the linked site currently only has 189 items why not rip it one time onto another sheet and update as you see fit.

Seems a lot of work for what doesn't appear to change often.

PS. Added with edit. It's clunky but a while back to click things on the site we used the SendKeys() function to tab to the item and send the enter key to fire off the button.

Thank you very much diafol! Now that you've helped me get this figured out and it works, I can proceed with my next task of implementation.

This is my first time on this website and this is the first article that caught my eye when I searched for VBA. My background is Access/VBA/Excel. Sorry if I'm a little off topic on an old post, but I wanted to make you aware of an alternate way to import an HTML table that doesn't require code and is done in a WYSIWYG interface.

Excel and Power BI both contain Power Query, which will let you easily connect to a data source like a web page, .xls, .csv, or database. If the data has clutter in and around it the Power Query Editor will let you filter it in a jiffy. Once the data is loaded into an Excel worksheet, manipulating it with VBA or importing to a database is simple for the average developer.

Here's the video I made: Import an HTML table with Power Query

If you're looking for another website HTML table to test with, try the IMDB Top 250 movie list.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.