error parsing HTML with excel vba

Question

So due to constraints, I need to parse some ugly html with excel vba. the problem with the HTML is that it has no element IDs. I have a page that has many unlabeled tables that each have a couple rows. The only thing I can build from is that there is an identifier in one of the cells that I need to pull. Every time the ID "xtu_id" appears as a value in a cell in a row of a table, I want to pull the data from that row. So it looks like this:

<tr>



<td>

                    col1

</td>


<td>

                    col2

</td>


<td>

                    xtu_id

</td>


<td>

                    col4

</td>


</tr>

Now that I see xtu_id exists in this row, I want to dump all cells of that row into an excel sheet. Here is what I used from reading other stackoverflow posts:

Sub CommandButton1_Click()

    Dim appIE As InternetExplorerMedium
    Set appIE = New InternetExplorerMedium

    With appIE
        .Navigate "https://my_website"
        .Visible = True
    End With

    Do While appIE.Busy Or appIE.ReadyState <> 4
        DoEvents
    Loop

    Set mydata = appIE.Document.getElementsByTagName("tr")

    For Each e In mydata
        For Each c In e
            If c.Cells().innerText Like "xtu_id" Then
                myValue = c.Cells().innerText
                MsgBox (myValue)
            End If
        Next c
    Next e
    Set appIE = Nothing

End Sub

This code works until I get to the [for each...] statement, I have trouble looping through each cell of each row to search for the "xtu_id" text. Any ideas on how to do this?

Scott Holtzman · Accepted Answer · 2017-04-17 23:32:58Z

1

Try this:

For Each c In e.Cells
    If c.innerText Like "xtu_id" Then
        myValue = e.innerText
        MsgBox (myValue)
    End If
Next c

answered Apr 17, 2017 at 23:32

Scott Holtzman

27.3k5 gold badges42 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

barker Over a year ago

Thanks for the quick reply Scott, I swapped that in and did not get a value, but the code did not error out. perhaps my if statement isn't true

Scott Holtzman Over a year ago

did you step through line-by-line and debug? An answer I provided earlier may help.

barker Over a year ago

Thanks for the help Scott, so I swapped e.innertext for c.innertext to pull the cell values and got what I was after. Your answer was correct, it was my fault for not being clear what string I wanted to pull. Thanks again!

Collectives™ on Stack Overflow

error parsing HTML with excel vba

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related