VBScript error getting Excel Application.Calculation Property

oraclexview
oraclexview used Ask the Experts™
on
Hello:
I'm having issues with a simple VBScript that grabs both the Excel Application.CalculationState Property value and the Excel Application.Calculation Property value and then displays both values to the screen.

The screen displays the proper value from the Application.CalculationState Property of 0 to the screen.  However, when trying to display the proper value from the Application.Calculation Property, the script errors on line 5 with the following message:
Line: 5
Column: 1
Error: Type mismatch: 'Calculation'
Code: 800A00D
Source: Microsoft VBScript runtime error
System: The data is invalid.

I can't seem to figure out what I did wrong.  Much help would be appreciated.

Thanks!
Dim objExcel

Set objExcel = CreateObject("Excel.Application")
Wscript.Echo "Calculation State: " & objExcel.CalculationState
Wscript.Echo "Calculation: " & objExcel.Calculation

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011

Commented:
Hello oraclexview,

If you want the value then try:

Regards,

chris_bottomley
Set objExcel = getObject(,"Excel.Application") 
Wscript.Echo "Calculation State: " & objExcel.CalculationState 
Wscript.Echo "Calculation: " & cint	(objExcel.Calculation)

Open in new window

Author

Commented:
chris_bottomley:
No, that did not work.I get the same error code with 'CInt' being the mismatch type.
Top Expert 2008
Commented:
The Calculation property is usually undefined when the application first starts.

Set objExcel = getObject(,"Excel.Application")
Wscript.Echo "Calculation State: " & objExcel.CalculationState
Calculation = "Undefined"
On Error Resume Next
Calculation = objExcel.Calculation
On Error GoTo 0
Wscript.Echo "Calculation: " & Calculation

Kevin
Top Expert 2008
Commented:
If you are opening a workbook later in the script, do that and then display the calculation property.

Kevin

Author

Commented:
zorvek:
You were right!  The Calculation property was undefined, so using your code to define it by setting it to a defined variable worked.  Also, I do want to add, it only worked if I did this after opening a workbook first.

I've attached my updated code with test screen display output for those that will find this useful on EE!  I sure did!  Thanks again zorvek!

I'm also going to increase the point value a bit too, since you provided two pieces to the puzzle. Cheers!
Dim objExcel
Dim objExcelFile
Dim Calculation

Set objExcel = CreateObject("Excel.Application")
If IsObject(objExcel) = True Then
	Wscript.Echo "objExcel is a valid object in this script"
	If IsNumeric(objExcel.CalculationState) = True Then
		Wscript.Echo "Calculation State is a numeric value in this script"
		Wscript.Echo "Calculation State: " & objExcel.CalculationState
		Set objExcelFile = objExcel.Workbooks.Open("G:\Books\Comics\ComicFileList.xlsx", 1, False)
		If IsObject(objExcelFile) = True Then
			Wscript.Echo "objExcelFile is a valid object in this script"
			If IsObject(objExcel.Calculation) = True Then
				Wscript.Echo "Application Calculation is a valid object in this script"
				Wscript.Echo "Calculation: " & objExcel.Calculation
				objExcelFile.Close
				objExcel.Quit
			Else
				Wscript.Echo "Application Calculation is NOT a valid object in this script"
				Calculation = "Undefined"
				Wscript.Echo "Calculation: " & Calculation
				Calculation = objExcel.Calculation
				Wscript.Echo "Calculation: " & Calculation
				objExcelFile.Close
				objExcel.Quit
			End If
		Else
			Wscript.Echo "objExcelFile is NOT a valid object in this script"
		End If
	Else
		Wscript.Echo "Calculation State is NOT a numeric value in this script"
	End If
Else
	Wscript.Echo "objExcel is NOT a valid object in this script"
End If

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial