0

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?

3
Contributors
9
Replies
36
Views
8 Months
Discussion Span
Last Post by Start4me
0

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.

Edited by diafol

0

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

0

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.

0

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.

Edited by Start4me: Code update

1

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.

1

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.

Edited by diafol

0

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.

Edited by rproffitt: Added PS.

0

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 question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.