CODEWITHSUNDEEP
excelvbaexcel-web-query
nightTrevors
nightTrevors

Reputation: 649

Web Query VBA Refresh

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.

Upvotes: 0

Views: 16237

Answers (2)

George
George

Reputation: 693

I had the same issue once , in one of my solutions, I had to refresh the Web Query results within some macro execution, via VBA.

Simplest solution I found is 

   'refresh data extracted from webpage
    ActiveWorkbook.RefreshAll

Perhaps you could use this too.

Upvotes: 1

Santosh
Santosh

Reputation: 12353

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

Upvotes: 1

Related Questions