等到 ActiveWorkbook.RefreshAll 完成 - VBA

Wait until ActiveWorkbook.RefreshAll finishes - VBA

提问by Mo2

I have a sub that calls on ActiveWorkbook.RefreshAllto bring new data in from an XML source, and then performs multiple modifications to it. The problem is that not enough time is given for the RefreshAll command to finish, so the following subs and functions end up not executing correctly, which result in repeated rows not being correctly erased.

我有一个 sub 调用ActiveWorkbook.RefreshAll从 XML 源引入新数据，然后对其执行多次修改。问题是没有足够的时间来完成 RefreshAll 命令，因此以下子函数和函数最终无法正确执行，从而导致重复行没有被正确擦除。

I have tried using Application.Waitand the Sleepfunction, but they seem to pause the refresh process too. I simply want the rest of the code to wait until the refresh process finishes before executing the rest of the code.

我曾尝试使用Application.Wait和Sleep函数，但它们似乎也暂停了刷新过程。我只是希望其余代码在执行其余代码之前等待刷新过程完成。

Any ideas on how to implement this? Right now I was only able to fix it by not calling on RefreshAll, which gives me the idea of implementing a second flow to be executed afterwards, but that's not a good workaround.

关于如何实现这一点的任何想法？现在我只能通过不调用 RefreshAll 来修复它，这让我想到了实施第二个流程以在之后执行，但这不是一个好的解决方法。

Please let me know if any of this wasn't clear. Thanks

如果有任何不清楚的地方，请告诉我。谢谢

EDITSo I tried a few suggestions from the posts below, and this is what I was able to come up with. Doing a "record macro" and then UNCHECKING the "Enable background refresh" in the table properties did not result in anything. I did a refresh as well afterwards. This was the result of the recorded macro:

编辑所以我从下面的帖子中尝试了一些建议，这就是我能够想出的。执行“记录宏”然后取消选中表属性中的“启用后台刷新”并没有产生任何结果。之后我也刷新了。这是录制宏的结果：

With ActiveWorkbook.Connections("XMLTable")
        .Name = "XMLTable"
        .Description = ""
End With
ActiveWorkbook.Connections("XMLTable").refresh

The class ActiveWorkbook.Connectionsdoes NOT have a BackgroundQueryoption so that I can set it to False. Any ideas?

类ActiveWorkbook.Connections没有BackgroundQuery选项，因此我可以将其设置为 False。有任何想法吗？

Just to be clear. This is an XML file hosted on a website which Excel goes and imports into a table. I then call that data into a pivot and other things. The goal here is to allow the import process from the website to the table to finish BEFORE executing any other commands. Thanks

只是要清楚。这是一个托管在网站上的 XML 文件，Excel 将其导入到表格中。然后我将这些数据称为数据透视和其他东西。这里的目标是允许从网站到表的导入过程在执行任何其他命令之前完成。谢谢

EDIT2:After a little more research, I have found this page: http://www.mrexcel.com/forum/excel-questions/564959-execute-code-after-data-connection-refresh-finished.htmlIt appears that an XML type of connection does not have a BackgroundQueryboolean. That option is only available for ODBC and OLEDB connections, which are types xlConnectionTypeODBC and xlConnectionTypeOLEDB, respectively. The XML connection I am using is of type xlConnectionTypeXMLMAP which does not have a BackgroundQueryoption. Does anyone have any idea on where to go from here? The only solution I have in mind right now is to make two seperate macro buttons on the excel sheet, one for refreshing and one for data modification, but I'd rather keep that option to the very last.

EDIT2：经过多一点研究，我找到了这个页面：http: //www.mrexcel.com/forum/excel-questions/564959-execute-code-after-data-connection-refresh-finished.html看来XML 类型的连接没有BackgroundQuery布尔值。该选项仅适用于 ODBC 和 OLEDB 连接，它们分别是 xlConnectionTypeODBC 和 xlConnectionTypeOLEDB 类型。我使用的 XML 连接是 xlConnectionTypeXMLMAP 类型，它没有BackgroundQuery选项。有没有人知道从这里去哪里？我现在想到的唯一解决方案是在 Excel 工作表上制作两个单独的宏按钮，一个用于刷新，一个用于数据修改，但我宁愿将这个选项保留到最后。

回答by Valiante

I had the same issue, however DoEventsdidn't help me as my data connections had background-refresh enabled. Instead, using Wayne G. Dunn's answer as a jumping-off point, I created the following solution, which works just fine for me;

我遇到了同样的问题，但是DoEvents没有帮助我，因为我的数据连接启用了后台刷新。相反，使用Wayne G. Dunn的答案作为起点，我创建了以下解决方案，这对我来说效果很好；

Sub Refresh_All_Data_Connections()

    For Each objConnection In ThisWorkbook.Connections
        'Get current background-refresh value
        bBackground = objConnection.OLEDBConnection.BackgroundQuery

        'Temporarily disable background-refresh
        objConnection.OLEDBConnection.BackgroundQuery = False

        'Refresh this connection
        objConnection.Refresh

        'Set background-refresh value back to original value
        objConnection.OLEDBConnection.BackgroundQuery = bBackground
    Next

    MsgBox "Finished refreshing all data connections"

End Sub

The MsgBoxis for testing only and can be removed once you're happy the code waits.

该MSGBOX仅用于测试，一旦你快乐的代码等待即可消除。

Also, I prefer ThisWorkbookto ActiveWorkbookas I know it will target the workbook where the code resides, just in case focus changes. Nine times out of ten this won't matter, but I like to err on the side of caution.

此外，与ActiveWorkbook 相比，我更喜欢ThisWorkbook，因为我知道它将针对代码所在的工作簿，以防焦点发生变化。十分之九这无关紧要，但我喜欢谨慎行事。

EDIT:Just saw your edit about using an xlConnectionTypeXMLMAP connection which does not have a BackgroundQuery option, sorry. I'll leave the above for anyone (like me) looking for a way to refresh OLEDBConnection types.

编辑：刚刚看到您关于使用没有 BackgroundQuery 选项的 xlConnectionTypeXMLMAP 连接的编辑，抱歉。我会把上面的内容留给任何人（像我一样）寻找刷新 OLEDBConnection 类型的方法。

回答by subro

Though @Wayne G. Dunn has given in code. Here is the place when you don't want to code. And uncheck to disable the background refresh.

尽管@Wayne G. Dunn 已经给出了代码。这是您不想编码的地方。并取消选中以禁用后台刷新。

回答by robotik

DISCLAIMER: The code below reportedly casued some crashes! Use with care.

免责声明：据报道，下面的代码导致了一些崩溃！小心使用。

according to THIS answerin Excel 2010 and above CalculateUntilAsyncQueriesDonehalts macros until refresh is done
ThisWorkbook.RefreshAll
Application.CalculateUntilAsyncQueriesDone

根据Excel 2010 及更高版本中的此答案会CalculateUntilAsyncQueriesDone暂停宏，直到完成刷新
ThisWorkbook.RefreshAll
Application.CalculateUntilAsyncQueriesDone

回答by tim.s

You must turn off "background refresh" for all queries. If background refresh is on, Excel works ahead while the refresh occurs and you have problems.

您必须为所有查询关闭“后台刷新”。如果后台刷新处于打开状态，Excel 会在刷新发生时提前工作并且您遇到问题。

Data > Connections > Properties > (uncheck) enable background refresh

数据 > 连接 > 属性 >（取消选中）启用后台刷新

回答by rwb

Try executing:

尝试执行：

ActiveSheet.Calculate

I use it in a worksheet in which control buttons change values of a dataset. On each click, Excel runs through this command and the graph updates immediately.

我在工作表中使用它，其中控制按钮更改数据集的值。每次单击时，Excel 都会运行此命令，图形会立即更新。

回答by Scott

This may not be ideal, but try using "Application.OnTime" to pause execution of the remaining code until enough time has elapsed to assure that all refresh processes have finished.

这可能并不理想，但请尝试使用“Application.OnTime”暂停剩余代码的执行，直到经过足够的时间以确保所有刷新过程都已完成。

What if the last table in your refresh list were a faux table consisting of only a flag to indicate that the refresh is complete? This table would be deleted at the beginning of the procedure, then, using "Application.OnTime," a Sub would run every 15 seconds or so checking to see if the faux table had been populated. If populated, cease the "Application.OnTime" checker and proceed with the rest of your procedure.

如果刷新列表中的最后一个表是一个仅包含一个表示刷新已完成的标志的假表怎么办？该表将在过程开始时删除，然后使用“Application.OnTime”，每 15 秒左右运行一次 Sub，检查是否已填充假表。如果已填充，请停止“Application.OnTime”检查器并继续您的程序的其余部分。

A little wonky, but it should work.

有点不稳定，但它应该可以工作。

回答by Wayne G. Dunn

Here is a solution found at http://www.mrexcel.com/forum/excel-questions/510011-fails-activeworkbook-refreshall-backgroundquery-%3Dfalse.html:

这是在http://www.mrexcel.com/forum/excel-questions/510011-fails-activeworkbook-refreshall-backgroundquery-%3Dfalse.html 上找到的解决方案：

Either have all the pivotcaches' backgroundquery properties set to False, or loop through all the workbook's pivotcaches:

要么将所有数据透视缓存的 backgroundquery 属性设置为 False，要么循环遍历所有工作簿的数据透视缓存：

Code:
    For Each pc In ActiveWorkbook.PivotCaches
       pc.BackgroundQuery = False
       pc.Refresh
    Next 

this will leave all pivotcaches backgroundquery properties as false. You could retain each one's settings with:

这将使所有 pivotcaches backgroundquery 属性保留为 false。您可以通过以下方式保留每个人的设置：

Code:

代码：

For Each pc In ActiveWorkbook.PivotCaches
  originalBGStatus = pc.BackgroundQuery
  pc.BackgroundQuery = False
  pc.Refresh
  pc.BackgroundQuery = originalBGStatus
Next

回答by MostFire

This worked for me:

这对我有用：

ActiveWorkbook.refreshall
ActiveWorkbook.Save

When you save the workbook it's necessary to complete the refresh.

保存工作簿时，必须完成刷新。

回答by James Koch

I tried a couple of those suggestions above, the best solution for me was to disable backgroundquery for each connection.

我尝试了上面的一些建议，对我来说最好的解决方案是禁用每个连接的后台查询。

With ActiveWorkbook.Connections("Query - DL_3").OLEDBConnection
    .BackgroundQuery = False
    End With

回答by AlexExcelStarter

For Microsoft Query you can go into Connections --> Properties and untick "Enable background refresh".

对于 Microsoft Query，您可以进入 Connections --> Properties 并取消勾选“Enable background refresh”。

This will stop anything happening while the refresh is taking place. I needed to refresh data upon entry and then run a userform on the refreshed data, and this method worked perfectly for me.

这将在刷新发生时停止发生任何事情。我需要在输入时刷新数据，然后在刷新的数据上运行用户表单，这种方法对我来说非常有效。