Making and using Excel Web Queries

Making and Using Microsoft Excel web queries

By Jim Gordon

Web queries bring HTML data tables from the web directly into Microsoft Excel.
These instructions explain how to make and use web queries for Microsoft Excel on your Mac.
These instructions are for all versions of Microsoft Excel from 2011 through 2021. This includes all license types including Office 365 subscriptions.

Data from Web pages

Data tables in web pages can be displayed using a variety of programming techniques. The web query technique works only with tables displayed on web pages using Hypertext Markup Language (HTML). Excel reads the HTML code of the web page and looks for table tags that specify the beginning and end of a table. If you use the View Code capability of most web browsers, you will find HTML tables with these beginning and ending tags:
<HTML> through </HTML>

Data can be displayed in rows and columns that look exactly like HTML data tables using other methods, such as Javascript.
Excel web query is not able to detect tables that are the result of alternative techniques.

Get data tables from a web page using a web query

You can get HTML row and column data by running a web query. A web query is a text file having a file extension of .iqy.
A web query file contains the URL of the web page that holds the data.
The query becomes part of the Excel worksheet. The result set of a query is called a QueryTable.

Make a web query file

The simplest web query consists of a text file containing the URL of a web page which has an HTML table. To make your web query, take the following steps:

Open Microsoft Word to a new, blank document
Type or paste the URL of a web page that has an HTML table into your document. In this example use http://www.agentjim.com/. When pasting a URL, use the little widget that appears after pasting and choose Keep Text Only from the pop-up menu.
Use File > Save As and choose file format Plain Text (.txt). Give the text file a sensible name and note where your file will be saved. Click Save.
The File Conversion dialog opens. In the options for Text Encoding choose MS-DOS, and in the Options section click the check box for Insert Line Breaks. Then click the OK button to complete saving your file.
In Finder, navigate to the file you just saved. Change the file extension from .txt to .iqy. You have to do this in Finder because Word won't let you change the file extension form .txt to .iqy when you save the file.

Run the web query

Your Mac must have a live internet connection to run queries on web pages that are found on the internet.

There are two ways to run a web query in Microsoft Excel. The first is by using the menus. The second is by using Visual Basic for Applications.

From the Menu Bar (not the Ribbon), use the Data menu. Choose Get External Data > Run Web Query. (Older versions of Excel say Run Saved Query).

The Choose a Query dialog displays defaulting to the Queries folder.
Select a .iqy query file to run in the Choose a Query dialog.
Click the Get Data button.
The Returning Data to Excel dialog opens. You can choose a cell that will be used for the upper left cell of the imported table. The default cell is the cell that was selected when you started these steps. You can choose Properties options now or later.
Click the OK button. Wait as Excel imports the data from the web page. Import speed depends on network speed and how much data you import.

Make and Run a query using a VBA (Visual Basic for Applications) macro

Press Option + F11 to display the Visual Basic Editor (VBE). On some keyboards you must press fn+option+F11
From the Menu Bar in the Visual Basic Editor choose Insert > Module to display a new, blank editing page.
Type or paste this example code into the blank editing page (substitute the URL of the web page from which you want to extract data)

Sub MakeWebQuery()
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.domainname.com", Destination:= _
        Range("A1"))
        .PostText = "local"
        .Name = False
        .FieldNames = False
        .RefreshStyle = xlInsertDeleteCells
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .HasAutoFormat = True
        .RefreshOnFileOpen = 1
        .BackgroundQuery = False
        .TablesOnlyFromHTML = True
        .SaveData = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

Press Command-Q to return to the Excel grid
To run the code, use the Menu Bar and choose Tools > Macro > Macros. Select your Macro and then click the Run button
For more examples including how to make dynamic web queries please see this page.

To refresh the data from the web page, select any cell within the query results. Then on the Menu Bar choose Data > Refresh. You may also use the Data Tab on the Ribbon and choose Refresh.

Web query results are displayed differently in Excel from the way the look on a web page. Formatting is minimally retained. Hyperlinks may or may not work. "Relative" hyperlinks and specialty hyperlink protocols will not work when clicked.

Working with query result sets

The result set querytable of a web query has many properties you can control. When you first make your web query, there are settings available to you in the Returning External Data to Excel dialog (see Figure 1).

Figure 1 - Returning External Data to Excel

Returning Data to Excel dialog screen shot

Click the Properties button to display the External Data Range Properties dialog when you first make a query. You can also display this dialog by right-clicking into the querytable result set and choosing Data Range Properties from the pop-up menu. The options presented are context sensitive. Not all options are available for web queries.

Figure 2 - External Data Range Properties

If your web query is dynamic (requests a parameter), the Parameters button becomes available in the Return External Data to Excel dialog box. Click the Parameters button to display the Parameters dialog, (see Figure 2). In this dialog you can modify the text of the prompt, enter a value to always use, or choose a cell and use that cell's value to satisfy the prompt. The Parameters dialog can also be displayed by right-clicking in the querytable result set of a parameter query and choosing Parameters from the pop-up menu.

Note the option to "Fill down formulas in columns adjacent to data." Enabling this option lets you use calculated columns with the web query result set.

Figure 3 - Parameters