vba 在时间表内同时刷新外部数据源和数据透视表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16222091/
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 both the External data source and pivot tables together within a time schedule
提问by user42995
In my last post Auto refresh pivottables data in excel on first run, i found that on my first executionthe queryfrom the External data sourceis refreshed and takes approximately 1 min to execute. and in my second run, the pivot tables are updated.
在我的上一篇文章首次运行时自动刷新 excel 中的数据透视表数据中，我发现在我第一次执行时，来自外部数据源的查询被刷新，执行大约需要 1 分钟。在我的第二次运行中，数据透视表被更新。
Is there a solution (VBA code) to refresh both the External data source and pivot tables together within a time schedule (If suppose we set a timer) by clicking command button?
是否有解决方案（VBA 代码）可以通过单击在时间安排（如果假设我们设置一个计时器）内同时刷新外部数据源和数据透视表command button？
回答by Alex
Under the connection properties, uncheck "Enable background refresh". This will make the connection refresh when told to, not in the background as other processes happen.
在连接属性下，取消选中“启用后台刷新”。这将使连接在被告知时刷新，而不是在其他进程发生时在后台刷新。
With background refresh disabled, your VBA procedure will wait for your external data to refresh before moving to the next line of code.
禁用后台刷新后，您的 VBA 过程将等待外部数据刷新，然后再转到下一行代码。
Then you just modify the following code:
然后你只需修改以下代码：
ActiveWorkbook.Connections("CONNECTION_NAME").Refresh
Sheets("SHEET_NAME").PivotTables("PIVOT_TABLE_NAME").PivotCache.Refresh
You can also turn off background refresh in VBA:
您还可以在 VBA 中关闭后台刷新：
ActiveWorkbook.Connections("CONNECTION_NAME").ODBCConnection.BackgroundQuery = False
回答by jpuck1054700
I used the above answer but made use of the RefreshAll method. I also changed it to allow for multiple connections without having to specify the names. I then linked this to a button on my spreadsheet.
我使用了上面的答案，但使用了 RefreshAll 方法。我还更改了它以允许多个连接而无需指定名称。然后我将它链接到我的电子表格上的一个按钮。
Sub Refresh()
Dim conn As Variant
For Each conn In ActiveWorkbook.Connections
conn.ODBCConnection.BackgroundQuery = False
Next conn
ActiveWorkbook.RefreshAll
End Sub
回答by Ejaz Ahmed
I think there is a simpler way to make excel wait till the refresh is done, without having to set the Background Query property to False. Why mess with people's preferences right?
我认为有一种更简单的方法可以让 excel 等到刷新完成，而不必将 Background Query 属性设置为 False。为什么要搞乱人们的喜好呢？
Excel 2010 (and later) has this method called CalculateUntilAsyncQueriesDone and all you have to do it call it after you have called the RefreshAll method. Excel will wait till the calculation is complete.
Excel 2010（及更高版本）有此方法称为 CalculateUntilAsyncQueriesDone，您只需在调用 RefreshAll 方法后调用它即可。Excel 将等到计算完成。
ThisWorkbook.RefreshAll
Application.CalculateUntilAsyncQueriesDone
I usually put these things together to do a master full calculate without interruption, before sending my models to others. Something like this:
在将我的模型发送给其他人之前，我通常会将这些东西放在一起，不间断地进行主完整计算。像这样的东西：
ThisWorkbook.RefreshAll
Application.CalculateUntilAsyncQueriesDone
Application.CalculateFullRebuild
Application.CalculateUntilAsyncQueriesDone
回答by Mark S.
I found this solution online, and it addressed this pretty well. My only concern is looping through all the pivots and queries might become time consuming if there's a lot of them:
我在网上找到了这个解决方案，它很好地解决了这个问题。我唯一担心的是循环遍历所有数据透视表，如果有很多数据透视表，查询可能会变得很耗时：
Sub RefreshTables()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Dim objList As ListObject
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
For Each objList In ws.ListObjects
If objList.SourceType = 3 Then
With objList.QueryTable
.BackgroundQuery = False
.Refresh
End With
End If
Next objList
Next ws
Call UpdateAllPivots
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
Sub UpdateAllPivots()
Dim pt As PivotTable
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.RefreshTable
Next pt
Next ws
End Sub
回答by FABZ
Auto RefreshWorkbook for example every 5 sec. Apply to module
例如每 5 秒自动刷新工作簿。 适用于模块
Public Sub Refresh()
'refresh
ActiveWorkbook.RefreshAll
alertTime = Now + TimeValue("00:00:05") 'hh:mm:ss
Application.OnTime alertTime, "Refresh"
End Sub
Apply to Workbook on Open
在打开时应用于工作簿
Private Sub Workbook_Open()
alertTime = Now + TimeValue("00:00:05") 'hh:mm:ss
Application.OnTime alertTime, "Refresh"
End Sub
:)
:)
