I am having a problem with coding. I used excel VBA to extract data from a webpage to excel. The webpage is https://proptx.midland.com.hk/utx/index.jsp?est_id=E12837&lang=en Click "All transaction" and it displays a second table at the bottom of the first table. I would like to extract data from the bottom table (not the top one).
Here is the code:
Sub PropertyTransactions()
Dim ieObj As InternetExplorer
Dim htmlEle As IHTMLElement
Dim i As Integer
i = 1
Set ieObj = New InternetExplorer
ieObj.Visible = True
ieObj.navigate "https://proptx.midland.com.hk/utx/index.jsp?est_id=E12837&lang=en"
Application.Wait Now + TimeValue("00:00:05")
For Each htmlEle In ieObj.document.getElementsByClassName("tablesorter")(0).getElementsByTagName("tr")
Set htmlEle = ActiveDocument.all.tags("head").Item(0)
With ActiveSheet
.Range("A" & i).Value = htmlEle.Children(0).textContent
.Range("B" & i).Value = htmlEle.Children(1).textContent
.Range("C" & i).Value = htmlEle.Children(2).textContent
.Range("D" & i).Value = htmlEle.Children(3).textContent
.Range("E" & i).Value = htmlEle.Children(4).textContent
.Range("F" & i).Value = htmlEle.Children(5).textContent
.Range("G" & i).Value = htmlEle.Children(6).textContent
.Range("H" & i).Value = htmlEle.Children(7).textContent
.Range("I" & i).Value = htmlEle.Children(8).textContent
.Range("J" & i).Value = htmlEle.Children(9).textContent
End With
i = i + 1
Next htmlEle
End Sub
However, there is an error in this line:
For Each htmlEle In ieObj.document.getElementsByClassName("tablesorter")(0).getElementsByTagName("tr")
It displays a run-time error 91: object variable or with block variable not set. How can I fix it? Thank you very much!
Set htmlEle = ActiveDocument.all.tags("head").Item(0)should probably be deleted. You usually don't want to modify your looping object variable within the loop.