"Calculate" an Excel Workbook 1

[ActMod]

I need to "Calculate" several Excel Worksheets within Access VBA. I can Calculate individual worksheets as follows:

Dim WkBk as Excel.Workbook
WkBk.Sheets("Sheet1&quot.Calculate
WkBk.Sheets("Sheet2&quot.Calculate

But I cannot figure out how to calculate the entire workbook with a single command. I tried things like:

WkBk.Calculate

But that gives me an error message. Is there a way to calculate an entire Excel Workbook with a single statement?

Thanks much for any help.

Jim

 

[jebry]

Hi Jim!

With the sheet names you've given you can do it in a loop:

For i = 1 to MaxNumberOfSheets
SheetName = "Sheet" & Format(i)
WkBk.Sheets(Sheetname).Calculate
Next i

hth


Jeff Bridgham
bridgham@purdue.edu

 

[rdodge]

If using Excel 2002 (not sure if it's in Excel 2000, and it's NOT in Excel 97), you can use the Excel's application variable to apply the CalculateFullRebuild on it like:

Application.Calculate (Same as Pressing F9, which is not too reliable)

Application.Calculate (Same as pressing Ctrl-Alt-F9)

Application.CalculateFullRebuild (Same as pressing Ctrl-Alt-Shift-F9)

Remember, if you are in some other program's VBA code, you need to refer to Excel's Application Object, such as if you use the variable name of "xlApp", then it would be:

xlApp.CalculateFullRebuild (or one of the other application level calculation methods)

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[MichaelRed]

from the ubiquitous {F1} (a.k.a. HELP):


Calculate Method


Calculates all open workbooks, a specific worksheet in a workbook, or a specified range of cells on a worksheet, as shown in the following table.

To calculate Follow this example
All open workbooks Application.Calculate (or just Calculate)
A specific worksheet Worksheets(1).Calculate
A specified range Worksheets(1).Rows(2).Calculate


Syntax

expression.Calculate

expression Optional for Application, required for Worksheet and Range. An expression that returns an object in the Applies To list.

(sans formatting)




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[rdodge]

opps, the second one should have been:

Application.CalculateFull

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.