VBA excel对于网格表中的单元格的电子表格中的表匹配单元格中的每一行 [英] VBA excel For each row in table match cell in spreadsheet with cell in webpage table
问题描述
这是一个重组我的问题的转贴,但是:
我试图将我的电子表格单元格B1文本与第10列中的所有单元格匹配网页上的一张桌子如果theres一个匹配,我想复制该行单元格4文本。到目前为止,我有:
Dim colRows As Object
Dim objDataGrid As Object
Dim xobj1 As Object
Dim xcel As Object
设置objDataGrid = IE.Document.getElementById(DataGridReservations)
设置colRows = objDataGrid.getElementsByTagName(tr)
对于每个元素在colRows
设置xcel = colRows.getElementsByTagName(td)
如果Range(B1)。Text = xcel.Item(9).innertext Then
Range H&(ActiveCell.Row))= xcel.Item(3).innertext
Else
范围(H&(ActiveCell.Row))=0
结束如果
退出
下一个
我得到一行错误
设置xcel = colRows.getElementsByTagName ....
把我的头发拉出来。另外,为了确保对于ColRows中的每个元素元素只会引用我在set colRows中定义的getElementsbyTagName(tr)。我们可以有更多的成功机会:
解决方案>
Sub sof20255214WebpageCell()
Dim colRows As Object
Dim objDataGrid As Object
Dim xobj1 As Object
Dim元素
Dim xcel As Object
Dim IE
设置IE = CreateObject(InternetExplorer.Application)
IE.navigatehttp://www.example.com/DataGridPage.php
While(IE.Busy或IE.READYSTATE<> 4)
DoEvents
Wend
设置objDataGrid = IE.Document.getElementById(DataGridReservations)
设置colRows = objDataGrid.getElementsByTagName(tr)
对于ColRows中的每个元素
设置xcel = element.getElementsByTagName(td)
如果Range(B1)。Text = xcel.Item(9).innerText Then
Range(H&( ActiveCell.Row))= xcel.Item(3).innerText
Else
范围(H&(Acti veCell.Row))=0
结束如果
退出
下一个
IE.Quit
End Sub
无论如何,我们无法使用(BAD):
设置xcel = colRows.getElementsByTagName(td)
由于colRows是行的集合,而不是单个行对象。不过,您可以使用(Good):
设置xcel = colRows.Item(0).getElementsByTagName(td)
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?
解决方案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对于网格表中的单元格的电子表格中的表匹配单元格中的每一行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!