vba 刷新工作簿中的所有查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31476040/
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
Refresh All Queries in Workbook
提问by Simon Paris
This works for .xls books, but can it be altered for .xlsx workbooks as well? Or is their syntax that will work for both?
这适用于 .xls 书籍，但也可以更改为 .xlsx 工作簿吗？或者他们的语法对两者都适用？
Option Explicit
Public Sub RefreshQueries()
Dim wks As Worksheet
Dim qt As QueryTable
For Each wks In Worksheets
For Each qt In wks.QueryTables
qt.Refresh BackgroundQuery:=False
Next qt
Next wks
Set qt = Nothing
Set wks = Nothing
End Sub
EDIT -- So it seems my syntax does refresh .xlsx workbooks, but not queries that are from sql server. How can those be refreshed via VBA.
编辑 - 所以看起来我的语法确实刷新了 .xlsx 工作簿，但不是来自 sql server 的查询。如何通过 VBA 刷新这些内容。
回答by nekomatic
First, no macro will work in a .xlsxworkbook because .xlsx workbooks can't contain macros - you need to save as a macro-enabled workbookwhich has the extension .xlsm.
首先，没有宏将在.xlsx工作簿中工作，因为 .xlsx 工作簿不能包含宏 - 您需要另存为具有扩展名的启用宏的工作簿.xlsm。
In Excel 2007 and later, user created external data connections to SQL Server data sources (amongst others) will result not in a QueryTables member, but in a ListObject which will possess a QueryTable object that can be accessed via the ListObject.QueryTableproperty - see Dick Kusleika's answerto this question. The following code should refresh both types of queries:
在 Excel 2007 及更高版本中，用户创建到 SQL Server 数据源（以及其他）的外部数据连接不会产生 QueryTables 成员，而是产生一个 ListObject，该对象将拥有可通过ListObject.QueryTable属性访问的 QueryTable 对象- 请参阅 Dick Kusleika 的回答对这个问题。以下代码应刷新两种类型的查询：
Option Explicit
Public Sub RefreshQueries()
Dim wks As Worksheet
Dim qt As QueryTable
Dim lo As ListObject
For Each wks In Worksheets
For Each qt In wks.QueryTables
qt.Refresh BackgroundQuery:=False
Next qt
For Each lo In wks.ListObjects
lo.QueryTable.Refresh BackgroundQuery:=False
Next lo
Next wks
Set qt = Nothing
Set wks = Nothing
End Sub
I wasn't previously familiar with the ListObjecttype so I don't know if you can have a ListObjecton a worksheet that doesn't have a QueryTable, which might cause an error in the above code - you might need to check for this.
我以前不熟悉该ListObject类型，所以我不知道您是否可以ListObject在没有 的工作表上使用QueryTable，这可能会导致上述代码中出现错误 - 您可能需要检查这一点。
回答by ChrisB
The answer from @nekomatic throws error 1004 for me (although it apparently works for others). I use this instead:
@nekomatic 的答案为我抛出错误 1004（尽管它显然适用于其他人）。我用这个代替：
Public Sub RefreshAllQueries()
' Refresh all queries (tables querying data from another source).
Dim iWorksheet As Excel.Worksheet
Dim iTable As Excel.ListObject
Dim iQueryTable As Excel.QueryTable
' Check each worksheet.
For Each iWorksheet In Excel.ActiveWorkbook.Worksheets
' Check each table.
For Each iTable In iWorksheet.ListObjects
If iTable.SourceType = Excel.XlListObjectSourceType.xlSrcQuery Then
' Table is a query table.
With iTable.QueryTable
.BackgroundQuery = False ' setting to wait for query to refresh
.Refresh
End With
End If
Next iTable
For Each iQueryTable In iWorksheet.QueryTables
iQueryTable.Refresh BackgroundQuery:=False ' wait for query to refresh
Next iQueryTable
Next iWorksheet
End Sub
回答by Frank Edwards
ActiveWorkbook.RefreshAll
ActiveWorkbook.RefreshAll
Objects that have the BackgroundQuery property set to True are refreshed in the background
将 BackgroundQuery 属性设置为 True 的对象在后台刷新
