Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Excel workbooks.saveas() error in powershell

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 ?

like image 905
Loïc MICHEL Avatar asked Sep 02 '25 08:09

Loïc MICHEL


1 Answers

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)
like image 180
Ansgar Wiechers Avatar answered Sep 05 '25 01:09

Ansgar Wiechers