Excel workbooks.saveas() error in powershell
Constants passed into the SaveAs
method usually represent numeric values, not strings. In your case the second parameter probably should be 51 (xlWorkbookDefault
) as documented here
. Same goes for the other two strings ("xlNoChange"
, which should be 1, and "xlLocalSessionChanges"
, which should be 2). You need to either use the numeric values, or define the constants yourself, e.g.:
$xlWorkbookDefault = 51
$xlNoChange = 1
$xlLocalSessionChanges = 2
Also, you cannot use $null
for arguments that should retain default values. Use [Type]::Missing
instead.
Change this:
$opendoc.saveas($basename,$saveFormat,$null,$null,$false,$false,"xlNoChange","xlLocalSessionChanges")
into this:
$opendoc.SaveAs($basename, 51, [Type]::Missing, [Type]::Missing, $false, $false, 1, 2)
Loïc MICHEL
I'm a technical architect at SPEIG (Colas group, the creator of the solar road WattWay and the dynamic road markings Flowell). With proven skills in Windows systems, scripting, database administration, I'm mainly dealing with Active Directory and System Center suite. I love to play with powershell and create web applications using php and javascript. My attempt to create a Powershell template system : https://github.com/kayasax/PowershellTemplate
Updated on June 18, 2022Comments
-
Loïc MICHEL almost 2 years
I'm trying to convert .xls files to .xlxs I tried many variations of this code but every time i'm facing this error message :
Exception lors de l'appel de « SaveAs » avec « 2 » argument(s) : « La méthode SaveAs de la classe Workbook a échoué. » Au caractère C:\temp\xlsx.ps1:18 : 6
- try{$opendoc.saveas($basename, $saveFormat)}
- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
- CategoryInfo : NotSpecified: (:) [], MethodInvocationException
- FullyQualifiedErrorId : ComMethodTargetInvocation
Here is my code :
$excel = new-object -comobject excel.application $excel.Visible = $false $saveFormat = "xlOpenXMLWorkbook"; ls c:\temp\*.xls | %{ $opendoc = $excel.workbooks.open($_.FullName) $excel.DisplayAlerts =$false $basename = $_.basename try{ $opendoc.saveas($basename,$saveFormat,$null,$null,$false,$false,"xlNoChange","xlLocalSessionChanges") # tried this one and got same error : $opendoc.saveas($basename, $saveFormat)} } catch{ $opendoc.close();$excel.quit() $_ } $opendoc.close(); } $excel.quit()
do you know the working recipe please ?