Saving a .xlsm file type in Excel using Windows Powershell

Question

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

So do I just add the line to where I have xlworkbookdefault? — Solerius
– Solerius, Commented Jan 14, 2020 at 21:51
I am sorry but this did not resolve my issue. I tried it this morning and I get the same exact error. I am kind of a newb when it comes to powershell, please forgive my ignorance. I know I need to use $xlFixedFormat in my SaveAs code but I cannot find a way to make it work. — Solerius
– Solerius, Commented Jan 15, 2020 at 14:28
Untested, but I'm guessing it should be $WB.SaveAs($FilePath,$xlFixedFormat) from reading the docs, after changing xlWorkbookDefault to xlOpenXMLWorkbookMacroEnabled. — BigBen
– BigBen, Commented Jan 15, 2020 at 14:29

BigBen · Accepted Answer · 2020-01-15 14:50:57Z

1

From the XlFileFormat docs, you want xlOpenXMLWorkbookMacroEnabled for XLSM files.

$xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlOpenXMLWorkbookMacroEnabled
...
$WB.SaveAs($FilePath,$xlFixedFormat)

answered Jan 15, 2020 at 14:50

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

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

Collectives™ on Stack Overflow

Saving a .xlsm file type in Excel using Windows 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