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.
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