0

I have major difficulties escaping a semicolon in my powershell script.
At first I open up a excel application, create a workbook and worksheet. So far so good. I can write to cells but when I try to write a semicolon to a cell, an error (HRESULT: 0x800A03EC) occurs.

$rootsheet.Cells(5,5)='=SUM(A1;A2)'

For example the semicolon in this SUM-function causes the error. I tried various possible escape format but nothing seems to work. The solutions in the following articles do not apply in this case:
escape-ampersands-semicolons
what-is-the-literal-escape-character-in-powershell

daniel
  • 3
  • 1
  • 2
    A guess, but try `'=SUM(A1,A2)'`. Excel may require you to use the comma as a separator when writing the formula programmatically. – BigBen Jul 27 '20 at 13:48
  • @BigBen, that's correct. I just gave it a try. Seems PowerShell needs you to input formulas with the `,` as a parameter seperator. Note, Excel will translate this back to the semi-colon. – JvdV Jul 27 '20 at 13:52

1 Answers1

0

Use the , as the formula separator instead of ; when inputting a formula programmatically. Excel will "translate" this back to the semi-colon:

'=SUM(A1,A2)'
BigBen
  • 46,229
  • 7
  • 24
  • 40