Check if workbook is waiting to calculate

[sime]

Hi listers

Is there a way to programatically check whether a workbook is waiting
to be refreshed? I am looking for something like:

Thisworkbook.NeedsCalculating

I am forcing a calculation of a worksheet with the following code:

***code start***
With ThisWorkbook.Worksheets("MyReport")

.EnableCalculation = True
ThisWorkbook.RefreshAll
.EnableCalculation = False

End With
***code end***

....however, if I click the sheet while the calculation is occuring, the
calculation cancels and the code continues to run without having
refreshed properly. How can I check if the calculation has completed?

Hope someone can help
Regards
Simon Hobbs

 

[Jim Cone]

Simon,

Untested but it ought to work...
Application.StatusBar = "Starting Refresh" 'new line
With ThisWorkbook.Worksheets("MyReport")
.EnableCalculation = True
ThisWorkbook.RefreshAll
.EnableCalculation = False
End With
Application.StatusBar = "Refresh Complete" 'new line
'other code
Application.StatusBar = False 'new line

Regards,
Jim Cone
San Francisco, USA

 

[sime]

Hi Jim
I need to be a bit more specific. Here's a modification of the code
with pseudo-code to represent what I want to do.

With ThisWorkbook.Worksheets("MyRep­ort")

.EnableCalculation = True
ThisWorkbook.RefreshAll
While ThisWorkbook.NeedsRefreshing = True
Msgbox "Please wait for the report to refresh."
ThisWorkbook.RefreshAll
Wend
.EnableCalculation = False

End With


That is, at the point where the workbook has supposedly refreshed, I
need to determine if it actually has. If it hasn't, I need to take
measures to enforce the calculation.

Regards
Simon

 

[sime]

http://groups.google.com.au/group/m...780a696a33c/657d9f20574fead3#657d9f20574fead3