在时间表内一起刷新外部数据源和数据透视表

In my last post Auto refresh pivottables data in excel on first run, i found that on my first execution the query from the External data source is 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代码）通过单击命令按钮在一个时间表内刷新外部数据源和数据透视表（如果我们设置一个计时器）？

4 个解决方案

#1

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

#2

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

#3

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

#4

Auto Refresh Workbook 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

适用于Open的工作簿

Private Sub Workbook_Open()
alertTime = Now + TimeValue("00:00:05") 'hh:mm:ss
Application.OnTime alertTime, "Refresh"
End Sub

#1