VBA Pull HTML Table into Excel

Excel Macro that will pull all the tables out of a webpage into Excel.  Just put the URL of the webpage in the search box  and click the “PULL Table from Webpage” button.  The VBA will pull HTML Table from webpage into Excel.

VBA Pull HTML Table to Excel Tool (69 downloads)

 

 

VBA to pull HTML Tables into Excel

VBA is looping all the table tags on the webpage, i.e. <table>, <thead>,<tbody>,<tr>,<th>,<td>, and grabbing the innerHTML of each tag.

Sub PullHTMLTable()
'
' PullHTMLTable Macro
'
Sheets("Data").Select
Range(Cells(7, 1), Cells(ActiveSheet.UsedRange.Rows.Count, ActiveSheet.UsedRange.Columns.Count)).Delete

Dim i As Long, strText As String

Dim doc As Object, hTable As Object, hBody As Object, hTR As Object, hTD As Object
 Dim tb As Object, bb As Object, tr As Object, td 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

Dim ie As SHDocVw.InternetExplorer
Set ie = New InternetExplorerMedium
Set ie = CreateObject("InternetExplorer.Application")
 While ie.busy
 DoEvents
 Wend
 ie.Visible = True
 While ie.busy
 DoEvents
 Wend
Dim NavURL As String
NavURL = Cells(3, 3).Value

ie.Navigate NavURL
 While ie.busy
 DoEvents
 Wend
 Set doc = ie.document
 Set hTable = doc.GetElementsByTagName("table")


 y = 2 'Column B in Excel
 z = 7 'Row 7 in Excel
 For Each tb In hTable
 Set hHead = tb.GetElementsByTagName("thead")
 For Each hh In hHead
 Set hTR = hh.GetElementsByTagName("tr")
 For Each tr In hTR
 
 
 Set hTD = tr.GetElementsByTagName("th")
 y = 1 ' Resets back to column A
 For Each th In hTD
 ws.Cells(z, y).Value = th.innertext
 y = y + 1
 Next th
 DoEvents
 z = z + 1
 Next tr
 Exit For
 Next hh
 'Exit For
 
 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
 ws.Cells(z, y).Value = td.innertext
 y = y + 1
 Next td
 DoEvents
 z = z + 1
 Next tr
 Exit For
 Next bb
 z = z + 1
 'Exit For
 Next tb


End Sub

Interested in learning new skills?