Extract data from html table using VBA

[Start4me]

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?

[diafol]

Can you show the relevant html from the webpage?

[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.

[Start4me]

Here's the webpage html before my code runs:

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

[Start4me]

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.

[Start4me]

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.

[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.

[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.

[rproffitt]

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.

[Start4me]

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.

[Ben_32]

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.