I am trying to open, write, & save an existing Excel file using PowerShell w/ COM Objects. I need to use the COM objects route because the existing Excel file uses formulas that the Import/Export-Excel PowerShell module does not honor. Excel 2016 is installed on the server & the PowerShell script is being kicked off as SYSTEM.
Code I have is:
$excel = New-Object -ComObject Excel.Application
$excelWorkbook = $excel.Workbooks.Open($ServerBuildTrackingFilePath)
$excelWorksheet = $excel.WorkSheets.item("VirtualServerBuild")
$excelWorksheet.Activate()
$xlDown = -4121
$xlUp = -4162
$nextRow = ($excelWorksheet.cells.Range("A1048576").End($xlUp).row) + 1
$excelWorksheet.Cells.Item($nextRow,1) = 'This'
$excelWorksheet.Cells.Item($nextRow,2) = 'is a'
$excelWorksheet.Cells.Item($nextRow,3) = 'Test'
$excelWorkbook.Save()
$excelWorkbook.Close()
$excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
Error returned is:
Microsoft Excel cannot access the file '\\domainfilepath\to\ServerBuildTracking.xlsx'. There
are several possible reasons:
• The file name or path does not exist.
• The file is being used by another program.
• The workbook you are trying to save has the same name as a currently open workbook.
At line:7 char:1
+ $excelWorkbook = $excel.Workbooks.Open($ServerBuildFilePath ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : OperationStopped: (:) [], COMException
+ FullyQualifiedErrorId : System.Runtime.InteropServices.COMException
The official answer is, "No, you can't do that like that."
Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment.
Since Excel.Application opens an instance of Excel to operate, that means what you're attempting is explicitly not supported.
You can find workarounds online that will get it working, but in my experience they're very fragile and prone to breaking when Office or Windows is updated.
I believe the official solution is to use the OpenXML SDK, which is intended for headless operation. However, I've never attempted that (ImportExcel module works well for me) so I don't know how feasible or difficult that is.
My comment on the ImportExcel module would be if you're using the Import-Excel and Export-Excel commands, or if you're using the Open-ExcelPackage command. The latter is much more programmatic manipulation similar to the COM object, but I'm not sure if it will clobber your formulas. If it does clobber your formulas, I'd suggest submitting a bug to the ImportExcel project, because that shouldn't happen.