Export HTML Table to Excel – When Web query does not work
To Import a HTML table in a web-page we can use,
- Web Query option in Excel to import it to Excel or
- From IE browser, right click on the Web Page Table & choose option “Export to Microsoft Excel“.
With some website this web query option will not work properly.
In that case, You can use this code to export HTML Table to Excel using VBA.
Also Read: Download File from Website Using Excel
Excel VBA HTML Table Import – Step By Step
Extract the HTML Table content of the web page. The article in this page explains about how to import the HTML content.
- In my Previous Article, the web page content is imported to Excel sheet as text content. But in this article, it is assigned to a HTMLFile Object which has more options to retrieve the HTML Tags.
- Once you have the web page content, it will have the Table related Tags like <Table>, <TR> and <TD>.
- Now the last step is to process each row & cell in that table and transfer it to the Excel Sheet.
VBA To Export HTML Table – Pull Website Table Data to Excel
Copy paste this code to Excel VB editor. Type web page URL that you want to scrape in cell A1 of worksheet1. Then
Sub Export_HTML_Table_To_Excel() Dim htm As Object Dim Tr As Object Dim Td As Object Dim Tab1 As Object 'Replace the URL of the webpage that you want to download Web_URL = VBA.Trim(Sheets(1).Cells(1, 1)) 'Create HTMLFile Object Set HTML_Content = CreateObject("htmlfile") 'Get the WebPage Content to HTMLFile Object With CreateObject("msxml2.xmlhttp") .Open "GET", Web_URL, False .send HTML_Content.Body.Innerhtml = .responseText End With Column_Num_To_Start = 1 iRow = 2 iCol = Column_Num_To_Start iTable = 0 'Loop Through Each Table and Download it to Excel in Proper Format For Each Tab1 In HTML_Content.getElementsByTagName("table") With HTML_Content.getElementsByTagName("table")(iTable) For Each Tr In .Rows For Each Td In Tr.Cells Sheets(1).Cells(iRow, iCol).Select Sheets(1).Cells(iRow, iCol) = Td.innerText iCol = iCol + 1 Next Td iCol = Column_Num_To_Start iRow = iRow + 1 Next Tr End With iTable = iTable + 1 iCol = Column_Num_To_Start iRow = iRow + 1 Next Tab1 MsgBox "Process Completed" End Sub
Press F5 to execute this code.
Excel will pull HTML table & align the content in the Excel sheet in proper format.
Also Read: Add Excel VBA Drop Down Data Validation List
Limitations of Parsing HTML <Table>
In many of the Website, even including Facebook, Twitter, the webpage will look like tables.
But they are embedded inside its HTML DIV & SPAN tags and not the <TABLE> tag.
The above code will work only if HTML has <table> tag. Other wise the parsing of the data has to be done using other DOM object commands.
It is better to use Facebook, Twitter API rather than just relying on HTML tags.