PowerShell Excel insert formula does not work-CodePudding

I have a .xlsm file where I have to insert formulae in specific cells. The formula I have to insert is '=LEFT(AG6,12)&RIGHT(1000 AG$1, 3)&RIGHT(AG6,5)' however after opening the file in Excel I see the formula I entered displayed in the cell instead of the result of the formula.

How do I get this to work?

$excelPath = '\a\bc\my.xlsm'
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
$excel.DisplayAlerts = $false
$book = $excel.workbooks.Open($excelPath)

$savePath = '\x\y\z\my.xlsm'

$ws = $book.workSheets('MySheet')

$ws.Cells(7, 33).Formula = '=LEFT(AG6,12)&RIGHT(1000 AG$1, 3)&RIGHT(AG6,5)'

$book.saveAs($savePath)

$book.close($false)

# Close all object references
$excel.Quit()
[void][System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)

CodePudding user response：

The cell is formatted as text. You can apply a General format prior to writing the formula.

$ws.Cells(7, 33).NumberFormat = 'General'
$ws.Cells(7, 33).Formula = '=LEFT(AG6,12)&RIGHT(1000 AG$1, 3)&RIGHT(AG6,5)'