Scrape html table for multiple web urls

pradeepvvce

New Member
Joined
Mar 20, 2018
Messages
1
Please find the below code for 1 url. How to scrape data for multiple urls that is pasted in sheet 1 of excel and result of the scraped data in sheet 2.

Sub Test()
Dim html As Object
Dim tbl As Object
Dim tRow As Object
Dim tCel As Object
Dim X As Long
Dim y As Long

With CreateObject("MSxml2.XMLHTTP")
.Open "GET", "https://www.cesco.com/Eaton-Cooper-Lighting-SHK-Cooper-Lighting-SHK-Fixture-Hook-With-2-1-2-Inch-Safety-Screw-Die-Cast-Aluminum/p2085509", False
.send
Set html = CreateObject("htmlfile")
html.body.innerHTML = .responseText
End With

For Each tbl In html.getElementsByTagName("table")
For Each tRow In tbl.getElementsByTagName("tr")
For Each tCel In tRow.getElementsByTagName("td")
y = y + 1
If y = 2 Or y = 4 Then
Cells(X + 1, IIf(y = 2, 1, 2)) = tCel.innerText
End If
Next tCel
y = 0
X = X + 1
Next tRow
Next tbl
End Sub
Sub Test()
Dim html As Object
Dim tbl As Object
Dim tRow As Object
Dim tCel As Object
Dim X As Long
Dim y As Long

With CreateObject("MSxml2.XMLHTTP")
.Open "GET", "https://www.cesco.com/Eaton-Cooper-Lighting-SHK-Cooper-Lighting-SHK-Fixture-Hook-With-2-1-2-Inch-Safety-Screw-Die-Cast-Aluminum/p2085509", False
.send
Set html = CreateObject("htmlfile")
html.body.innerHTML = .responseText
End With

For Each tbl In html.getElementsByTagName("table")
For Each tRow In tbl.getElementsByTagName("tr")
For Each tCel In tRow.getElementsByTagName("td")
y = y + 1
If y = 2 Or y = 4 Then
Cells(X + 1, IIf(y = 2, 1, 2)) = tCel.innerText
End If
Next tCel
y = 0
X = X + 1
Next tRow
Next tbl
End Sub
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Watch MrExcel Video

Forum statistics

Threads
1,109,068
Messages
5,526,594
Members
409,712
Latest member
lager2020

This Week's Hot Topics

Top