VBA web scrape include the header and html table

hi all,

I have VBA code that helps me to scrape a web page. The code below can only scrape the first table and it is without header.
I want to:
1. Search for all tables and copy the data into different worksheets separately starting at A1. (table 1 pastes to sheet1; table 2 pastes to sheet2, etc)
2. Copy all the data including the header.

Please Login or Register  to view this content.

@Norie i have changed code a bit, to include header and and take away the exit for. Still there is some improvement need. I am still working to improve my code.

Sub WebScrape()

Dim ie As Object, i As Long, strText As String

Dim doc As Object, hTable As Object, hBody As Object, hTR As Object, hTD As Object, hHead As Object
Dim tb As Object, bb As Object, tr As Object, td As Object, hth As Object, hh As Object

Dim y As Long, z As Long, wb As Excel.Workbook, ws As Excel.Worksheet

Set wb = Excel.ActiveWorkbook
Set ws = wb.ActiveSheet

Set ie = CreateObject("InternetExplorer.Application")
ie.Visible = False

y = 1 'Column A in Excel
z = 1 'Row 1 in Excel

ie.navigate "https://www.investing.com/rates-bonds/financial-futures", , , , "Content-Type: application/x-www-form-urlencoded" & vbCrLf

Do While ie.Busy: DoEvents: Loop
Do While ie.READYSTATE <> 4: DoEvents: Loop

Set doc = ie.document
Set hTable = doc.getElementsByTagName("table")
For Each tb In hTable
'tTable > thead > tr > th
Set hHead = tb.getElementsByTagName("thead")
For Each hh In hHead
Set hTR = hh.getElementsByTagName("tr")
For Each tr In hTR

Set hth = tr.getElementsByTagName("th")
y = 1 ' Resets back to column A
For Each th In hth
Debug.Print th.innerText
ws.Cells(z, y).Value = th.innerText
y = y + 1
Next th
DoEvents

Next tr
Exit For

Next hh
'th.innerText


Set hBody = tb.getElementsByTagName("tbody")
For Each bb In hBody

Set hTR = bb.getElementsByTagName("tr")
For Each tr In hTR

Set hTD = tr.getElementsByTagName("td")
y = 1 ' Resets back to column A
For Each td In hTD
Debug.Print td.innerText
z = 2
ws.Cells(z, y).Value = td.innerText
y = y + 1
Next td
DoEvents
z = z + 1
Next tr
Exit For
Next bb
'Exit For
Next tb

End Sub

@AB33, i tried method you mentioned, however, experience comes to me that script error. Therefore, i do not keep on using method you suggested.

No, it is not a method. You do not have to write any code. Just type the web address and choose the number of tables you want to download. There are 10 tables and I downloaded all in 20 seconds.