Pause code execution but let Excel work

Question

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

Open a file.
Wait till the file fills with data (this file uses cell formulae to download data from external database, the download takes approx 15 seconds)
After 20 seconds check if everything is downloaded, if no, wait further 10 seconds (up to minute total waiting).
If everything is downloaded, archive and close the file.

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:

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

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.

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

to wait few sec without suspending Excel try this solution

Kazimierz Jawor
– Kazimierz Jawor

2015-06-19 11:28:57 +00:00
Commented Jun 19, 2015 at 11:28 — Kazimierz Jawor
– Kazimierz Jawor, Commented Jun 19, 2015 at 11:28

Siddharth Rout · Accepted Answer · 2015-06-19 11:31:57Z

1

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

answered Jun 19, 2015 at 11:31

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Gary's Student Over a year ago

Consider using Now since Timer can fool you if you cross midnight.

jacek_wi Over a year ago

Unfortunately this answer doesn't work for me, I edited the question to add results of testing, including the code I have at the moment.

Siddharth Rout Over a year ago

That is because you are opening the file in the same Excel instance. So till the time the file is not opened, the rest of the code will not run.

jacek_wi Over a year ago

I think I don't understand what you say here. Just to try to make it more clear: all of my code executes as expected, but the Wait sub doesn't make the formulae update as I want them to - it seems it takes up enough system resources so that 60 seconds wait is not enough when, if opened manually, the file takes less than 20 seconds to update itself.

Collectives™ on Stack Overflow

Pause code execution but let Excel work

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related