Excel vba 刷新等待 [英] Excel vba refresh wait

问题描述

我正在创建一些代码，我可以在其中单击一个按钮，它会刷新我在该工作表上的查询表.

I am creating some code where I can click on a single button and it will refresh the querytables that I have on that sheet.

现在，我的问题是在刷新后我有更多的代码来复制一些信息，但是此代码在刷新开始后立即运行并且信息尚未被替换.

Now, my problem is that I have more code after the fresh that copies some of the information, but this code is being run right after the refresh has started and the information has not yet been replaced.

我想为刷新创建一个等待期，然后其余代码可以继续.

I want to create a waiting period for the refresh to complete and then the rest of the code can continue.

我不想只等5秒，而是等待刷新期，这样我就不会等待太长或太短，具体取决于网速等

I don't want to just wait for 5 seconds but for the refreshing period, so that I am not waiting too long or too short, depending on Internet speed etc.

我该怎么做?

简单代码:

ActiveWorkbook.RefreshAll

这里我需要延迟或等待代码，直到所有刷新完成...然后

Here I need the delay or waiting code till all the refreshing is finished... Then

MsgBox("The Refreshing is Completed!")

那个方向的东西.但是它不能在实际完成之前说出msgbox....有时根据互联网速度刷新需要更短或更长的时间，所以我希望它是实际刷新时间的变量.

Something in that direction. But it can't say the msgbox before it is actually finished.... Sometimes depending on internet speed the refreshing takes shorter or longer, so I want it to be a variable of the actual refreshing time.

推荐答案

我正在使用 PowerPivot 模型，我想在保存和关闭模型之前刷新数据.但是excel只是在刷新完成前关闭了模型，打开时模型又恢复了刷新.

I was working with a PowerPivot model, and I wanted to Refresh the data before I saved and closed the Model. However, excel just closed the model before the refresh was complete, and the model resumed refreshing on opening.

在 RefreshAll 方法之后添加以下行，成功了:

Adding the following line right after the RefreshAll method, did the trick:

ThisWorkbook.RefreshAll
Application.CalculateUntilAsyncQueriesDone

我希望它也适用于您.

确保您禁用事件以加快速度.

Make sure that you Disable Events to speed things up.

请注意，我使用的是 Excel 2010，我不确定此方法是否适用于旧版本.

Note that I am using Excel 2010, I am not sure if this method is available in older versions.

这篇关于Excel vba 刷新等待的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！