Refreshable Web Query Macro

[lforster]

Looking to write some VBA in excel to loop through refreshing a web query that has 2 variables I am trying to populate. After doing some digging online I found the following code which appears to have worked for some other users.

With ActiveSheet.QueryTables(1)
.Connection = "URL;" & NewURL
.Refresh
End With​

Unfortunately when I try to use this code I am getting an error for the first line of the code saying "Run-Time Error '9': Subscript out of range"

I tried changing the name of my web query and then replacing QueryTables(1) with the name I chose and that gave me the same error.

Hoping someone can help troubleshoot this issue as I have searched on google for a half hour or so and haven't been able to figure it out.

 

[John_w]

Welcome to MrExcel forums.

That error means there isn't a web query on the active sheet. Create a manual web query with the macro recorder running and then you can modify the generated code as required.

 

[offthelip]

I use this code in a loop to query lots of urls, you don'#t need to manually create the query so you can run it anywhere:
Change the string "connectstring" to your url
You may need to change some of the parameters depend what you are looking at, e.g webtables="8" is unlikely to work for you

       ConnectString = fullstring

        
        
        ' On the Workspace worksheet, clear all existing query tables
        For Each QT In ActiveSheet.QueryTables
            QT.Delete
        Next QT
        
        ' Define a new Web Query
        Set QT = ActiveSheet.QueryTables.Add(Connection:=ConnectString, Destination:=Range("A1"))
        With QT
            .Name = MyName
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .WebSelectionType = xlSpecifiedTables
            .WebFormatting = xlWebFormattingAll
            .WebTables = "8"
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .WebDisableRedirections = False
        End With
        
        ' Refresh the Query
        QT.Refresh BackgroundQuery:=False

 

[lforster]

Welcome to MrExcel forums.

That error means there isn't a web query on the active sheet. Create a manual web query with the macro recorder running and then you can modify the generated code as required.

I do have a web query in the active sheet, or at least I am going to the data tab and working through the "From Web" option. I have tried to do use the Macro Recorder to see how it generates the code but unfortunately it is not picking up a change in web address when I try that. It is actually referencing the name as the back half of the web URL I used to create the initial query but even if I try to use that reference in the code I posted in my initial comment I am getting the same error message. I can't say I am very familiar with web queries so it is possible that what I am calling a web query is different than what all of these posts I have been looking at are referencing.

 

[lforster]

In trying this I changed the code to the below and got an error saying Application-defined or Object-Defined error. When going through the code line by line the error is happening on the Set QT Line but I am not sure if I am adjusting the code correctly. I also tried directly referencing the website after the Connection:= and that gave the same error.

       ConnectString = "http://originzip=11111&destzip=22222[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana] [/FONT][/COLOR][COLOR=#222222][FONT=Verdana]       [/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]        ' On the Workspace worksheet, clear all existing query tables[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]        For Each QT In ActiveSheet.QueryTables[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]            QT.Delete[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]        Next QT[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]        [/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]        ' Define a new Web Query[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]        Set QT = ActiveSheet.QueryTables.Add(Connection:=ConnectString, Destination:=Range("A1"))[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]        With QT[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]            .Name = MyName[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]            .FieldNames = True[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]            .RowNumbers = False[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]            .FillAdjacentFormulas = False[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]            .PreserveFormatting = True[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]            .RefreshOnFileOpen = False[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]            .BackgroundQuery = False[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]            .RefreshStyle = xlInsertDeleteCells[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]            .SavePassword = False[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]            .SaveData = True[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]            .AdjustColumnWidth = True[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]            .RefreshPeriod = 0[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]            .WebSelectionType = xlSpecifiedTables[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]            .WebFormatting = xlWebFormattingAll[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]            .WebPreFormattedTextToColumns = True[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]            .WebConsecutiveDelimitersAsOne = True[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]            .WebSingleBlockTextImport = False[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]            .WebDisableDateRecognition = False[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]            .WebDisableRedirections = False[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]        End With[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]        [/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]        ' Refresh the Query[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]        QT.Refresh BackgroundQuery:=False

​

 

[John_w]

The Connection is missing the "URL;" for a web query:

[COLOR=#222222][FONT=Verdana]Set QT = ActiveSheet.QueryTables.Add(Connection:="URL;" & ConnectString, Destination:=Range("A1"))[/FONT][/COLOR]