Add Web Query To Worksheet Excel VBA Macro

Excel allows to easily import data from a webpage with the web query functionality. This example shows a simple routine to add a web query and import all tables from a particular website into the active worksheet.

Macro/VBA code:

 

  Sub GetPublicHolidays()

    Dim qt As QueryTable, qtCount As Integer, website As String

    qtCount = ActiveSheet.QueryTables.Count

    If qtCount > 0 Then ActiveSheet.QueryTables(1).Delete

    website = "https://www.timeanddate.com/holidays/spain"

    Set qt = ActiveSheet.QueryTables.Add( _

        Connection:="URL;" & website, _

        Destination:=ActiveSheet.Range("A1"))

    With qt

        .WebSelectionType = xlAllTables

        .Refresh

    End With

  End Sub

 

 

Macro explained:

• First we declare a variable (qt) as a QueryTable, an integer variable to hold the count of queries (qtCount), and a string variable to hold the url of the website.
• Then we assign the count of query tables (if any) in the active worksheet to gtCount. If the count is greater than 0 we delete the first query table as the new query table will take that position.
• Then we assign the web page of interest to the variable website. This can be updated as needed or set to a particular cell in the workbook.
• Now we add the web query to the active worksheet with QueryTables.Add, and assign it to the variable qt. The parameters Connection and Destination need to be specified within the parenthesis. In this case, the data source for the query table is a string in the form of URL.
• Finally we select all the tables in the query with the WebSelectionType method of the query variable qt and refresh to display the table. Note if there was a previous query it will move it forward to place the new query table in A1.

This is how we add a web query to a worksheet in Excel with VBA macros.

Other examples:

• How to Get Info of Selected Files with Excel VBA macros
• How to Crop Selection Window with Excel VBA macros
  
• How to create a Simple Digital Clock Timer with Excel VBA macros