暂停执行代码，但允许Excel工作 [英] Pause code execution but let Excel work

问题描述

我正在制作一个宏，我想做以下事情:

I am making a macro that I want to do the following things:

1. 打开文件.

1. Open a file.

等待直到文件填充数据为止(此文件使用单元格公式从外部数据库下载数据，下载过程大约需要15秒)

Wait till the file fills with data (this file uses cell formulae to download data from external database, the download takes approx 15 seconds)

我遇到的问题是停止执行代码一段时间，同时让公式同时进行更新-我尝试了Application.Wait，在MSDN上阅读后发现:

The problem I have is stopping code execution for some time while letting the formulae update themselves in the meantime - I tried Application.Wait after reading at MSDN that:

Wait方法会挂起所有Microsoft Excel活动，并且可能会在您等待Wait时阻止您在计算机上执行其他操作.但是，诸如打印和重新计算之类的后台过程仍在继续."

The Wait method suspends all Microsoft Excel activity and may prevent you from performing other operations on your computer while Wait is in effect. However, background processes such as printing and recalculation continue."

但是它不起作用-等待停止了代码和数据下载.有什么办法可以停止代码一段时间，但让所有其他Excel活动继续进行吗?我希望该宏能够完全在无人看管的情况下运行，因为它可能必须在深夜运行.

but it didn't work - Wait stopped both the code and the data download. Is there any way to stop the code for a while but let all other Excel activity continue? I'd like the macro to be able to run completely unattended as it is possible that it will have to be ran in the middle of the night.

尝试了Siddarth的回答，但对我不起作用.我尝试的代码:

tried Siddarth's answer and it doesn't work for me. The code I tried:

Sub processfile()

    Dim bbgwb As Workbook
    Dim filepath As String

    filepath = "C:\Test\0900CET.xls"
    Set bbgwb = Workbooks.Open(filepath)

    Wait 60

    filepath = "C:\Test\Archive\"
    If Len(Dir(filepath)) = 0 Then
        MkDir filepath
    Else
        'Do nothing.
    End If

    filepath = "C:\Test\Archive\0900.xls"

    bbgwb.Worksheets(1).Range("A1:AZ200").Copy
    bbgwb.Worksheets(1).Range("A1:AZ200").PasteSpecial xlPasteValuesAndNumberFormats
    bbgwb.Worksheets(1).Range("C142").Value = Now

    bbgwb.SaveAs Filename:=filepath, FileFormat:=56
    bbgwb.Close
    ThisWorkbook.Close

End Sub

Private Sub Wait(ByVal nSec As Long)
    nSec = nSec + Timer
    While nSec > Timer
        DoEvents
    Wend
End Sub

0900CET.xls具有公式，将其打开后会自动更新数据(然后要存储和存档，这就是为什么我在末尾添加整个复制/粘贴特殊部分的原因).我只是手动测试了0900CET.xls(在17.3秒后更新了所有公式)，并通过上面的代码(在归档文件之前没有更新任何一个单元格)来测试了

0900CET.xls has the formulae which, after opening it, autoupdate with data (which I want then store and archive, that's why I add the whole copy/paste special part at the end). I just tested opening 0900CET.xls manually (all formulae updated after 17.3 seconds) and via the code above (not a single cell updated before the file got archived.

推荐答案

我通常使用为自己创建的这个子

I usually use this sub that I created for my self

Sub Sample()
    '
    '~~> Do Something
    '
    Wait 5 '<~~ Wait for 5 seconds. Change as applicable
    '
    '~~> Do Something
    '
End Sub

Private Sub Wait(ByVal nSec As Long)
    nSec = nSec + Timer
    While nSec > Timer
        DoEvents
    Wend
End Sub

这篇关于暂停执行代码，但允许Excel工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！