Web Query VBA Refresh

nightTrevors picture nightTrevors · Oct 10, 2013 · Viewed 14.2k times · Source

If I have a web query connection established, what's the best way to update the URL and refresh the connection with VBA?

Here is basically what I want to do:

Sub RefreshWebQuery()

    Dim request As String
    request = 'some url constructed by concatenating cell values

    'set command text of my web query as request
    'update my query table

End Sub

I've seen various methods online but none are concise/worked for me.

Thanks in advance for your help.

Answer

Santosh picture Santosh · Oct 11, 2013

The best way to refresh a query is by setting .Refresh BackgroundQuery := False. The refresh is supposed to refresh the results. Also you can set .RefreshPeriod = 0.

Sub webquery()
    Dim url As String
    url = "URL;http://test.com"
    With Worksheets("Sheet1").QueryTables.Add(Connection:=url, Destination:=Worksheets("Sheet1").Range("A1"))
        .Name = "Geocoder Query"
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub