等到 ActiveWorkbook.RefreshAll 完成 - VBA [英] Wait until ActiveWorkbook.RefreshAll finishes - VBA

问题描述

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

I have a sub that calls on ActiveWorkbook.RefreshAll to 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.

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

I have tried using Application.Wait and the Sleep function, 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.

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

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.

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

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

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

EDIT So 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

ActiveWorkbook.Connections 类没有 BackgroundQuery 选项，因此我可以将其设置为 False.有什么想法吗?

The class ActiveWorkbook.Connections does NOT have a BackgroundQuery option so that I can set it to False. Any ideas?

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

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

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

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.html It appears that an XML type of connection does not have a BackgroundQuery boolean. 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 BackgroundQuery option. 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.

推荐答案

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

I had the same issue, however DoEvents didn'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;

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

MsgBox 仅用于测试，一旦您对代码等待感到满意，就可以将其删除.

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

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

Also, I prefer ThisWorkbook to ActiveWorkbook as 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.

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

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.

这篇关于等到 ActiveWorkbook.RefreshAll 完成 - VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！