Web 查询 VBA 刷新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19306832/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Web Query VBA Refresh
提问by nightTrevors
If I have a web query connection established, what's the best way to update the URL and refresh the connection with VBA?
如果我建立了 Web 查询连接，更新 URL 并刷新与 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.
在此先感谢您的帮助。
回答by Santosh
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.
刷新查询的最佳方法是设置.Refresh BackgroundQuery := False. 刷新应该刷新结果。您也可以设置.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
回答by George
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.
我曾经遇到过同样的问题，在我的一个解决方案中，我必须通过 VBA 在某些宏执行中刷新 Web 查询结果。
Simplest solution I found is
我发现的最简单的解决方案是
'refresh data extracted from webpage
ActiveWorkbook.RefreshAll
Perhaps you could use this too.
也许你也可以使用这个。
