jamescooper
Well-known Member
- Joined
- Sep 8, 2014
- Messages
- 840
Hello, I am trying to pull data xml from the web into 2 columns, A and B.
With the 2 topics below; topics and topics2, I need to try and pull it one - i.e. column A then B, rather than all of A's and then B.
I cannot quite get the code to loop correctly below, any ideas?
Thanks.
With the 2 topics below; topics and topics2, I need to try and pull it one - i.e. column A then B, rather than all of A's and then B.
I cannot quite get the code to loop correctly below, any ideas?
Thanks.
Code:
Public Sub Data_Pull_Products_and_Prices_2()
Dim http As Object, html As New HTMLDocument, topics As Object, topics2 As Object, titleElem As Object, topic As HTMLHtmlElement
Dim i As Integer
Dim j As Integer
Dim rngURL As Range
Dim LastRow As Long
Dim LastRow2 As Long
Application.ScreenUpdating = False
Set http = CreateObject("MSXML2.XMLHTTP")
For Each rngURL In Worksheets("Sheet1").Range("E1", Worksheets("Sheet1").Range("E" & Rows.Count).End(xlUp))
http.Open "GET", rngURL, False
http.send
html.body.innerHTML = http.responseText
DoEvents
Set topics = html.getElementsByClassName("product-details--wrapper")
i = 1
j = 1
For Each topic In topics
Set titleElem = topic.getElementsByTagName("div")(0)
Sheets(1).Cells(i, 1).Value = titleElem.getElementsByTagName("a")(0).innerText
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
i = 1 + LastRow
Set topics2 = html.getElementsByClassName("hidden-medium product-info-section-small")
For Each topic In topics2
Set titleElem = topic.getElementsByTagName("div")(0)
Sheets(1).Cells(j, 2).Value = titleElem.getElementsByTagName("p")(0).innerText
LastRow2 = Cells(Rows.Count, "B").End(xlUp).Row
j = 1 + LastRow2
Next
Next
Application.ScreenUpdating = True
End Sub