Programming Excel with VBA and .NET

Manage Web Queries

Most of the preceding samples get an existing QueryTable, modify its properties, and then call Refresh. I could have used the QueryTables collection’s Add method to create these queries on the fly; however, you need to remember to delete previously created QueryTables.

For example, the following code creates three new query tables on the active worksheet:

    Dim ws As Worksheet, qt As QueryTable, i As Integer
    Set ws = ActiveSheet
    For i = 1 To 3
        Set qt = ws.QueryTables.Add("URL;http://finance.yahoo.com/q/ecn?s=yhoo", [A12])
        qt.Name = "Temp Query"
        qt.WebTables = "22"
        qt.WebSelectionType = xlSpecifiedTables
        qt.WebFormatting = xlWebFormattingNone
        qt.BackgroundQuery = False
        qt.RefreshStyle = xlOverwriteCells
        qt.Refresh
    Next

When this code runs, it creates three query tables on the worksheet named Temp_Query, Temp_Query_1, and Temp_Query_2, respectively. There’s no easy way to manage query tables through the Excel user interface; however, if you press Ctrl-G, you’ll see the names for the new query tables listed in the Go To dialog box (Figure 24-8).

Figure 24-8. Excel automatically numbers query tables that have the same base name

It’s possible to manually delete query tables by going to the named range and selecting Clear All, but that leaves the name in the worksheet, and subsequent names will be indexed _4, _5, and so on. The easiest way to clean up mistaken or trial query ...