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.

Answer 1

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"

Answer 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 :)