Excel VBA html Table import - Export Web Table to Worksheet

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:

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.

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. More Tips: