How to Insert Excel Formula using PowerShell?

Question

The formula =LEFT(AB4,FIND(" ",AB5)-1 works perfectly in Excel, but seems to be causing errors in PowerShell where I get this error:

Exception from HRESULT: 0x800A03EC
At C:\Scripts\Excel_NUID2.ps1:21 char:1
+ $worksheet.range("AH5:AH$rows").formula = "=LEFT(AB4,FIND(" ",AB5)-1"
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : OperationStopped: (:) [], COMException
+ FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

My PowerShell Script Code;

#Open Up the Workbook#
$excel = new-object -comobject Excel.Application
$excel.visible = $false  
$workbook = 
$excel.workbooks.open("c:\Users\Jack\documents\NUID_Status_Report.xlsx") 
$worksheet = $workbook.Worksheets.Item(1)

$rows = $worksheet.range("A1").currentregion.rows.count

### Set up a filter ###
$headerRange = $worksheet.Range("a4","aj4")
$headerRange.AutoFilter() | Out-Null

#### Trims Password Expiration Date Name ###

$worksheet.range("AH4").formula = "Shortened Expiration Date"
[void]$worksheet.Cells.Item(1,1).select()
$excel.visible = $true

#### Trims Password Expiration Date Formula ###

$worksheet.range("AH5:AH$rows").formula = "=LEFT(AB4,FIND(" ",AB5)-1"
[void]$worksheet.Cells.Item(1,1).select()
$excel.visible = $true

You state 'The formula =LEFT(AB4,FIND(" ",AB5)-1 works perfectly in Excel' but that is incorrect; it is missing a closing bracket somewhere. — user4039065
– user4039065, Commented May 17, 2017 at 15:26
Correct, I must have missed the closing bracket by accident. The right should be =LEFT(AB4,FIND(" ",AB5)-1) — jreyez
– jreyez, Commented May 17, 2017 at 15:33

user4039065 · Accepted Answer · 2017-05-17 15:25:44Z

1

Quotes within a quoted string need to be doubled-up.

$worksheet.range("AH5:AH$rows").formula = "=LEFT(AB4,FIND("" "",AB5)-1)"
'you can also get rid of the inside quotes with the CHAR function
$worksheet.range("AH5:AH$rows").formula = "=LEFT(AB4, FIND(CHAR(32), AB5)-1)"

ASCII character 32 is a space. I've also added a bracket to make a legal formula.

answered May 17, 2017 at 15:25

user4039065

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

Collectives™ on Stack Overflow

How to Insert Excel Formula using PowerShell?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related