Hello all I am opening a .xlsm sheet in windows powershell then writing data to various cells from read-host variables. Everything works fine except when I go to save the file using powershell. It gives me this error quoted below and refuses to save it in .xlsm format the file is already a .xlsm file so im not converting it. I have no problems when using this code to save .xlsx formats, which is probably my problem. I am a bit of newb forgive my ignorance.
"This extension can not be used with the selected file type. Change the file extension in the File name text box or select a different file type by changing the Save as type."
Here's the code I am using to save to .xlsm. I have removed a lot of the file structure to protect privacy and made it simpler, my code works just fine but not the save to .xlsm part, please help...
Add-Type -AssemblyName Microsoft.Office.Interop.Excel
$xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlWorkbookDefault
$XL = New-Object -comobject Excel.Application
$Num = Read-Host - Prompt 'What is the number?'
$FilePath = "V:\$fileName.xlsm"
$workbook = $XL.Workbooks.Open($FilePath)
$WS = $workbook.Worksheets("Sheet1")
$ws.Cells.Item(2,29) = "$Num"
$XL.DisplayAlerts = $False
$WB.SaveAs($FilePath)
$WB.Close
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($XL) | Out-Null
From the XlFileFormat docs, you want xlOpenXMLWorkbookMacroEnabled for XLSM files.
$xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlOpenXMLWorkbookMacroEnabled
...
$WB.SaveAs($FilePath,$xlFixedFormat)