CODEWITHSUNDEEP
vbaexcel
user8226217
user8226217

Reputation:

Getting vba to wait before proceeding

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

Upvotes: 2

Views: 2183

Answers (1)

Nathan_Sav
Nathan_Sav

Reputation: 8531

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

Upvotes: 1

Related Questions