VBA excel For each row in table match cell in spreadsheet with cell in webpage table

Question

This is kind of a repost to reorganize my question but:

I'm trying to match my spreadsheets cell B1 text with all the cells in the 10th column of a table on a webpage. If theres a match, I want to copy that rows cell 4 text. So far I have:

Dim colRows As Object
Dim objDataGrid As Object
Dim xobj1 As Object
Dim xcel As Object

Set objDataGrid = IE.Document.getElementById("DataGridReservations")
Set colRows = objDataGrid.getElementsByTagName("tr")

For Each element In colRows
    Set xcel = colRows.getElementsByTagName("td")
        If Range("B1").Text = xcel.Item(9).innertext Then
        Range("H" & (ActiveCell.Row)) = xcel.Item(3).innertext
        Else
        Range("H" & (ActiveCell.Row)) = "0"
        End If
Exit For

Next

I'm getting an error at the line

set xcel = colRows.getElementsByTagName....

Pulling my hair out. Also, just to be sure, "For Each element in colRows" element will only refer to "getElementsbyTagName("tr")" that I defined in set colRows. it wont also pickup the td tags bracketed in tr right?

running it to reference a single row i had to write it as Range("B1").text or else it wouldnt read the cells content... i'm assuming since the cell is formatted as general not a number. Just tried to do it and it failed still. — TPJ87, Nov 28 '13 at 01:25

score 0 · Answer 1 · answered Nov 28 '13 at 13:26

We could have more chance for success with this:

Sub sof20255214WebpageCell()

  Dim colRows As Object
  Dim objDataGrid As Object
  Dim xobj1 As Object
  Dim element
  Dim xcel As Object

  Dim IE

  Set IE = CreateObject("InternetExplorer.Application")
  IE.navigate "http://www.example.com/DataGridPage.php"

  While (IE.Busy Or IE.READYSTATE <> 4)
    DoEvents
  Wend

  Set objDataGrid = IE.Document.getElementById("DataGridReservations")
  Set colRows = objDataGrid.getElementsByTagName("tr")

  For Each element In colRows
    Set xcel = element.getElementsByTagName("td")
    If Range("B1").Text = xcel.Item(9).innerText Then
      Range("H" & (ActiveCell.Row)) = xcel.Item(3).innerText
    Else
      Range("H" & (ActiveCell.Row)) = "0"
    End If
    Exit For
  Next

  IE.Quit

End Sub

Anyway, we cannot use this (BAD):

Set xcel = colRows.getElementsByTagName("td")

As colRows is a collection of rows, but not a single row object. Nevertheless, you can use this (Good):

Set xcel = colRows.Item(0).getElementsByTagName("td")

VBA excel For each row in table match cell in spreadsheet with cell in webpage table

1 Answers1

Linked