How to import a HTML Table to Excel Worksheet, when Web query does not work?
If any website has the data in the form of Tables, we use Web Query option in excel to import it to Excel or if we use IE as browser, then we can right click on the Web Page Table and choose option “Export to Microsoft Excel“.
With some website this web query option will not work properly. In this article we are going to see how to do data mining with just excel.
Also Read: Download File from Website Using Excel
Solution – Step By Step
Extract the HTML 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 and cell in that table and transfer it to the Excel Sheet.
Extract Web Page with Table & Process HTML TagsVisual 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
In many of the Website, even including Facebook, Twitter, the webpage will look like tables. But they are embedded inside DIV and SPAN tags.
It is better to use Facebook, Twitter API rather than just relying on HTML tags.
Previous Post: Export Outlook Emails To Excel – With Date and Time – Using VBA
Next Post: Excel VBA Change Cell Color Index RGB code
Extract Web page with Tables – Best Data Mining Tool