CODEWITHSUNDEEP
excelpowershellexcel-2003
Anthony Kong
Anthony Kong

Reputation: 40834

Powershell: how to set the calculation mode of excel application?

It is in Excel 2003 VBA

Application.Calculation = xlCalculationManual

I want to achieve the same in Powershell. It is what I did:

$excel = New-Object -com Excel.Application
$xlCalculationManual = -4135
$excel.Calculation = $xlCalculationManual

Value -4135 is obtained from there: http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.constants%28v=office.14%29.aspx

It throws an exception:

Exception setting "Calculation": "Unable to set the Calculation property of the Application class"
At C:\Documents and Settings\AUser\Desktop\ppp2eikon.ps1:11 char:8
+ $excel. <<<< Calculation = $xlCalculationManual
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : PropertyAssignmentException

What is the proper way to set this property?

Upvotes: 0

Views: 3587

Answers (2)

Jeffrey L Prochaska
Jeffrey L Prochaska

Reputation: 1

try:

$excel = New-object -ComObject excel.application
$workbook = $excel.WorkBooks.Open("filepath")
$Workbook.Parent.Calculation = -4135
...
...
#Turn recalculate back on
$Workbook.Parent.Calculation = 1

This works if you have Excel installed

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166790

At least in VBA, you can't set the calculation mode if there's not at least one open workbook. Error raised is: 1004 - Application-defined or object-defined error

Try opening a workbook first.

Upvotes: 1

Related Questions