How to import a HTML Table to Excel Worksheet, 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, We are going to see how to do data mining properly by Parsing HTML Table with just Excel VBA.
Also Read: Download File from Website Using Excel
Excel VBA Parse HTML Table – 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 Extract Web Page with Table & Process HTML Tags
Copy paste this code to Excel VB editor. Type web page URL that you want to scrape in cell A1 of worksheet1. ThenExcel VBA - HTML Table ImportVisual Basic1234567891011121314151617181920212223242526272829303132333435363738394041424344Sub HTML_Table_To_Excel()Dim htm As ObjectDim Tr As ObjectDim Td As ObjectDim Tab1 As Object'Replace the URL of the webpage that you want to downloadWeb_URL = VBA.Trim(Sheets(1).Cells(1, 1))'Create HTMLFile ObjectSet HTML_Content = CreateObject("htmlfile")'Get the WebPage Content to HTMLFile ObjectWith CreateObject("msxml2.xmlhttp").Open "GET", Web_URL, False.sendHTML_Content.Body.Innerhtml = .responseTextEnd WithColumn_Num_To_Start = 1iRow = 2iCol = Column_Num_To_StartiTable = 0'Loop Through Each Table and Download it to Excel in Proper FormatFor Each Tab1 In HTML_Content.getElementsByTagName("table")With HTML_Content.getElementsByTagName("table")(iTable)For Each Tr In .RowsFor Each Td In Tr.CellsSheets(1).Cells(iRow, iCol).SelectSheets(1).Cells(iRow, iCol) = Td.innerTextiCol = iCol + 1Next TdiCol = Column_Num_To_StartiRow = iRow + 1Next TrEnd WithiTable = iTable + 1iCol = Column_Num_To_StartiRow = iRow + 1Next Tab1MsgBox "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.
In many of the Website, even including Facebook, Twitter, the webpage will look like tables.
But they are embedded inside DIV and SPAN tags.
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.
Previous Post: Launch List of Application – Batch Execution
Next Post: Excel To VCF Converter 1.1 – Excel To Vcard – 68,000 Downloads
Extract Web Page with Tables – Parse HTML Table – Data Mining Tool