Wait till workbook opens and calculates

[fonzmck]

Hi...pretty new to all this.

I have a macro that opens up another workbook "CurveWorkBook", and copies values from there into the original workbook "Template".

The problem is that "CurveWorkBook" is huge. It has a number of macros on it and if used manually takes at least one hit of F9 and about a 3 to 4 second wait each time until everything runs, calculates and displays the values that I want to copy and paste into "Template".

If I run through my macro step by step using F8 it seems to work fine. So I thought the solution must be to try and make the macro wait while "CurveWorkBook" opens and does it's thing, before carrying on with the task. But I still the values dont seem to have time to update.

Anyone know what code I can use to open "CurveWorkBook", F9 it (at least once) and make sure that everything waits until it has had time to draw all it's data in and calculate it, before the macro continues? Will paste the relevant part of the code I have below. As you can see, Im trying a few different things!

Thanks a lot



Workbooks.Open (ThisWorkbook.Path & "\Pricing Sheets" & "\" & DataWorkbookName)
For Each ws In ThisWorkbook.Worksheets
ws.Calculate
Next ws
Answer = MsgBox("Give it 5 secs or so...", vbOKCancel, "Wait punk!")
Application.Calculate
Worksheets(1).Calculate
Range(DataArea).Select
Selection.Copy

 

[Joe Was]

This is a bit of code that traps the Read/Write state of a file, that is untill a file is fully open it is in a Read-Only status, this code loops until the file goes into a Write status. Which indicates that it is fully opened.


Sub myWaitForFileOpen()
Dim wb As Workbook

Set wb = Application.Workbooks.Open("C:\My Files\AAA.xls")

Do Until wb.ReadOnly = False
wb.Close

Application.Wait Now + TimeValue("00:00:01")

Set wb = Application.Workbooks.Open("C:\My Files\AAA.xls")
Loop

'Then the code that needs that Workbook open here!
'Or Call That other macro here!
End Sub

 

[fonzmck]

Thanks a lot for the response. It seems that that was not the problem though. I still think it has something to do with fully calculating the sheet.

To reiterate where I think it is going wrong, everything works fine when I go through the code step by step using F8. This is because doing it this way opens "CurveWorkBook" and then runs through it's subsequent code step by step (which takes a while manually). This completely calculates the sheet and my macro then continues on to copy the required values successfully.

But if I just run the macro, the values appear as #N/A or #VALUE! because it seems the "CurveWorkBook" is not properly calculating. The macro take about 4 seconds to run and as a further hint, if you open "CurveWorkBook" normally just manually, it usually requires at least one or two hits of F9 before everything feeds through, with about 3 seconds of calculation time each hit of F9. So running the macro seems to be too quick for "CurveWorkBook" to run through all it's code and produce the right values.

Make sense? Any ideas?

Thanks again in advance.

 

[MyriadRocker]

This is a bit of code that traps the Read/Write state of a file, that is untill a file is fully open it is in a Read-Only status, this code loops until the file goes into a Write status. Which indicates that it is fully opened.


Sub myWaitForFileOpen()
Dim wb As Workbook

Set wb = Application.Workbooks.Open("C:\My Files\AAA.xls")

Do Until wb.ReadOnly = False
wb.Close

Application.Wait Now + TimeValue("00:00:01")

Set wb = Application.Workbooks.Open("C:\My Files\AAA.xls")
Loop

'Then the code that needs that Workbook open here!
'Or Call That other macro here!
End Sub

I know this thread is kind of old but I had a question concerning this code. I am wanting to use this code to execute a macro in a seperate workbook than the code is in. So, I open WB2.xls from WB1.xls, executing a macro in WB2.xls from WB1.xls. The problem is that WB2.xls takes a while to load so I have wait until it's totally open to call the macro.

Now...this code will do that for me but...what if the WB2.xls workbook is read-only?