I was recently asked to repair a webquery which had gone bad. The company controlling the website had changed the layout of the content. I decided to extract the data from the website into excel using VBA and a call on the HTML tables inside of the website itself. Obviously this technique works best where there are dedicated tables on a website. It does not discriminate as all data from the page is imported but with some smart manipulation you can inject some VBA to extract the relevant tables into a fresh worksheet.
If you put the website address in A1 of sheet1 of your workbook the following will upload the tables from your website.
https://afd.calpoly.edu/web/sample-tables
The above is the web address I will be downloading. It is contained inside the file.
Option Explicit
Sub HTMLTable() 'Excel VBA to import website tables.
Dim htm As Object
Dim Tr As Object
Dim Td As Object
Dim Tab1 As Object
Dim URL As String
Dim Colstart As Long
Dim HTML As Variant
Dim i As Long
Dim j As Long
Dim n As Long
Application.ScreenUpdating = False
URL = VBA.Trim(Sheets(1).Cells(1, 1)) 'String
Set HTML = CreateObject("htmlfile") 'Create HTMLFile Object
With CreateObject("msxml2.xmlhttp") 'Get the WebPage Content
.Open "GET", URL, False
.send
HTML.Body.Innerhtml = .responseText
End With
Colstart = 1
j = 2
i = Colstart
n = 0
'Loop Through website tables
For Each Tab1 In HTML.getElementsByTagName("table")
With HTML.getElementsByTagName("table")(n)
For Each Tr In .Rows
For Each Td In Tr.Cells
Sheet1.Cells(j, i) = Td.innerText
i = i + 1
Next Td
i = Colstart
j = j + 1
Next Tr
End With
n = n + 1
i = Colstart
j = j + 1
Next Tab1
Application.ScreenUpdating = True
End Sub
The following Excel file has the VBA procedure and an Example. Enjoy!!!
