Getting vba to wait before proceeding

Question

I need to update 4 different workbooks, which all collect data from bloomberg. I tried to construct a new workbook which will automatically open them and then the code within the workbooks will get activated since the code gets activated whenever the workbook opens. However my macro opens all workbooks at the same time and the update is taking too long when they are open at the same time. I tried to use the command "doevents" and "Application.Wait (Now + TimeValue("0:03:30"))", however they do not work. I would like them to open one at the time, then let the calculation in the specific workbook end before opening the next the next workbook.

Here is my code:

    Sub UpdateWorkbooks()

    'Quick Financial Longer Series
     Workbooks.Open ("G:\FONDS\Quick financials_Longer Series.xlsb")
     Application.Wait (Now + TimeValue("0:03:30"))

    'Quick Financial
     Workbooks.Open ("G:\FONDS\Quick Financial\Auto\Quick financials.xlsb")
     Application.Wait (Now + TimeValue("0:03:30"))

    'Quick Intra Corr (SX5E)
     Workbooks.Open ("G:\FONDS\Quick Financial\Auto\Quick Intra Corr(SX5E).xlsb")
     Application.Wait (Now + TimeValue("0:03:30"))

    'SPX Sector Correlation
     Workbooks.Open ("G:\FONDS\SPX Sector Correlation.xlsb")
     Application.Wait (Now + TimeValue("0:03:30"))


     Workbooks("UpdateWorkbooks.xlsb").Close savechanges:=True


     End Sub

@danieltakeshi Not duplicate. This asks specifically for how to make excel open workbooks, execute the codes on said open workbook and then resume code. The TimeWait simply is what OP has tried so far. — Moacir
Instead of having the workbooks call the code through their Workbook open events, it may be better to explicitly call the code through your main workbook. This way, the run-time will automatically wait for the called process to finish before proceeding. — Brandon Barney
So I should call the different codes of the workbooks from my "Updateworkbook" module, instead of asking vba to open them? — user8226217
VBA runs on a single thread, so Open will return only once Workbook_Open() from the targeted workbook returns. If you want to speed-up the update, then open each workbook in a separate instance with shell: Shell "excel /e ""C:\files\workbook.xlsm""" or with CreateObject("WScript.Shell"). — Florent B.

Nathan_Sav Nathan_Sav · Accepted Answer · 2017-09-12T14:16:54

the application.calculationstate may help here. Wrap it up in a function, you could even return the state, and use a do until. Not sure of the infinite loop possibilities, so may be advisable to add a retry counter also.

 Select Case Application.CalculationState
    Case 0: strCalculationState = "Calculating"
    Case 1: strCalculationState = "Done"
    Case 2: strCalculationState = "Pending"
 End Select

Getting vba to wait before proceeding

1 Answers