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, Sep 12 '17 at 14:19
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, Sep 12 '17 at 14:22
So I should call the different codes of the workbooks from my "Updateworkbook" module, instead of asking vba to open them? — , Sep 12 '17 at 14:28
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., Sep 12 '17 at 16:06

score 1 · Answer 1 · answered Sep 12 '17 at 14:16

1

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

answered Sep 12 '17 at 14:16

Nathan_Sav

8,466
2
13
20

Would this enable the new workbook to execute the `On Workbook_Open` events mid vba execution? – Moacir Sep 12 '17 at 14:20
Im unsure of that code means, would you explain please. I thought it would be possible to let vba open the workbook, let the code inside of the workbook run and the proceed to the next one whenever the calculations are done. – Sep 12 '17 at 14:26
1

Probably best to add a `DoEvents` in the loop that is calling this. – Sep 12 '17 at 14:26

Getting vba to wait before proceeding

1 Answers1

Linked