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

问题描述

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

我尝试使用 Application.Wait 和睡眠功能，但它们似乎也暂停刷新过程。我只需要其余的代码等待刷新过程完成，然后执行其余的代码。

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

<请让我知道，如果这些都不清楚。感谢

编辑
所以我从下面的帖子中尝试了一些建议，这就是我能够想出的。
执行记录宏，然后在表属性中UNCHECKING启用后台刷新并不会产生任何结果。我以后也做了一个刷新。这是记录的宏的结果：

 使用ActiveWorkbook.Connections（XMLTable）
 .Name = XMLTable
 .Description =
 End with 
 ActiveWorkbook.Connections（XMLTable）。refresh 
  

类 ActiveWorkbook.Connections 不具有 BackgroundQuery 选项，以便我可以将其设置为False。任何想法？

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

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表单上创建两个单独的宏按钮，一个用于刷新，另一个用于数据修改，但是我宁愿保留最后一个选项。

解决方案

评论：

 '~~>您的代码在此之前刷新
 
 Activeworkbook.RefreshAll 
 DoEvents 
 
'~~>其余的代码在刷新
  

什么 DoEvents 确实就像是暂时允许Windows从 Macro 中休息，以便在返回宏之前处理所有挂起的事件。

所以当你刷新你的工作簿并将 DoEvents 之后，在刷新完成之前不会恢复宏。

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.

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.

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

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

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

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.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.

解决方案

As commented:

'~~> your code here before refresh

Activeworkbook.RefreshAll
DoEvents

'~~> rest of your code here after refresh

What DoEvents does is like momentarily allowing Windows to take a break from Macro to process all pending events before returning to the macro.
So when you refresh your workbook and put DoEvents after, it will not resume the macro until the refresh is finished.

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