Excel

Retrieve table content from a web page to Excel

Ease of Use

Easy

Version tested with

2000 

Submitted by:

lucas

Description:

Runs a web query to get the contents of a specific table on a web page and put it in cell A1 of Excel. 

Discussion:

You have a web page that is updated regularly and you wish to retrieve fresh data from the website and put it in an Excel file. This script will get the information for you and put it in Excel. The data must be in a table for this to work. You will need to count down the page to find out in which table your data is contained. This example uses the main VBAExpress Knowledgebase Page and the "Recent Entries" table which is the 2nd table on the web page. 

Code:

instructions for use 

			
Put this code In a standard module: 
 
Option Explicit 
Sub gethtmltable() 
    Dim objWeb As QueryTable 
    Dim sWebTable As String 
     'You have to count down the tables on the URL listed in your query
     'This example shows how to retrieve the 2nd table from the web page.
    sWebTable = 2 
     'Sets the url to run the query and the destination in the excel file
     'You can change both to suit your needs
    Set objWeb = ActiveSheet.QueryTables.Add( _ 
    Connection:="URL;http://www.vbaexpress.com/kb/default.php", _ 
    Destination:=Range("A1")) 
     
    With objWeb 
         
        .WebSelectionType = xlSpecifiedTables 
        .WebTables = sWebTable 
        .Refresh BackgroundQuery:=False 
        .SaveData = True 
    End With 
    Set objWeb = Nothing 
End Sub

How to use:

  1. Open the Visual Basic Editor by going to tools-Macro's-Visual Basic Editor or use Alt-F11
  2. On the toolbar of the Visual Basic Editor, go to insert - module
  3. In the module pane paste the code above.
  4. Close the Visual Basic Editor by clicking the X in the upper right corner or go to File-Close
 

Test the code:

  1. Run the macro by going to tools-Macro-Macro's and click on the gethtmltable macro.
  2. Click Run
  3. You can run the code in the example file by clicking the button, it is assigned to the macro.
 

Sample File:

Get_HTML_Table.zip 7.28KB 

Approved by mdmackillop


This entry has been viewed 335 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express