Exception when adding formula to an Excel cell using PowerShell

Question

I've tried a few ways I've seen on here to get this to work but with no success. Most say use .formula or escape characters. I was able to add normal data to cells but I can't add the formula. I know it works outside of Powershell.

Here's the line:

$WS.Cells.Item(6,4).formula = "='C:\Users\JG\Desktop\Reference\Financial\Test\[$MNE.xlsx]$MNE'!$O$28"

And the error:

Exception from HRESULT: 0x800A03EC At C:\Users\JG\Desktop\Reference\Test\Test2.ps1:47 char:1 + $WS.Cells.Item(6,4).formula = "='C:\Users\JG\Desktop\Reference\ ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : OperationStopped: (:) [], COMException + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

Any help is appreciated. Thanks.

You need to escape the dollar signs in the formula using a backtick - see here — BigBen
– BigBen, Commented Aug 29, 2018 at 1:22
That worked. I was sitting here escaping the single quote. Thank you! — Justin
– Justin, Commented Aug 29, 2018 at 1:39

BigBen · Accepted Answer · 2018-08-29 01:41:10Z

4

You need to escape the $ in the formula with a backtick `, otherwise $MNE,$O and $28 are interpreted as variables, with no value. The formula then becomes ='C:\Users\JG\Desktop\Reference\Financial\Test\[.xlsx]'!, which is invalid.

Your formula then should be:

"='C:\Users\JG\Desktop\Reference\Financial\Test\[`$MNE.xlsx]`$MNE'!`$O`$28"

answered Aug 29, 2018 at 1:41

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

The Fish · Accepted Answer · 2018-08-29 06:47:27Z

2

Another solution would be to swap the quotations around. A double quote in PowerShell is a normal string, variables will be expanded when it is eventually interpreted. A single quote in PowerShell is a literal string, no variable expansion can happen inside it.

For example the following $World will be interpreted as a variable and PowerShell will attempt to replace it with the $World variable

 > Write-Output "Hello $World"
Hello

Simply replacing the double quotes with a single quote will stop the variable expansion

> Write-Output 'Hello $World'
Hello $World

To apply it to your problem you would simply flip the quotations

$WS.Cells.Item(6,4).formula = '="C:\Users\JG\Desktop\Reference\Financial\Test\[$MNE.xlsx]$MNE"!$O$28'

Useful to keep in mind :)

answered Aug 29, 2018 at 6:47

The Fish

1,0395 silver badges6 bronze badges

2 Comments

BigBen Over a year ago

Note that Excel still requires the single quotes within the formula, 1 at the beginning of the path and 1 right after the sheet name. I don't think you can just flip the quotations in this particular case.

The Fish Over a year ago

I was a little unsure about how Excel would deal with the double quote. Not useful for this use case but still useful. :)

Collectives™ on Stack Overflow

Exception when adding formula to an Excel cell using PowerShell

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related