Powershell Excel SaveAs requires confirmation-CodePudding

I use below script to convert bunch of xls files to xlsx.

$folderpath = %tempPath%
$filetype ="*xls"

Add-Type -AssemblyName Microsoft.Office.Interop.Excel

$xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlWorkbookDefault
write-host $xlFixedFormat
$excel = New-Object -ComObject excel.application
$excel.visible = $true
Get-ChildItem -Path $folderpath -Include $filetype -recurse | 
ForEach-Object `
{
 $path = ($_.fullname).substring(0, ($_.FullName).lastindexOf("."))
 
 "Converting $path"
 $workbook = $excel.workbooks.open($_.fullname)

 $path  = ".xlsx"
 $workbook.saveas($path, $xlFixedFormat)
 $workbook.close()
 

}
$excel.Quit()
$excel = $null
[gc]::collect()
[gc]::WaitForPendingFinalizers()

It used to work perfectly running on VM. Unfortunately with changing folder path I realised there are popup windows to confirm saving that didn't come up before and the script gets stuck on that. Any simple corrections that could prevent that error?

"scriptError": {
    "localizedName": "Error",
    "value": "Unable to get the SaveAs property of the Workbook class\r\nAt C:\\Users\\~
    "variableName": "ScriptError"
}

CodePudding user response：

Here's an example of how I set the path when saving an Excel file using PowerShell. I set the path using a combination of the Get-Location cmdlet, Get-Date cmdlet and the file name, which is stored in a string variable for use when saving the script.

Add-Type -AssemblyName Microsoft.Office.Interop.Excel
$xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlWorkbookDefault
$htFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlHtml

$Date = get-date -format R
$CurrentLocation = Get-Location
$CurrentDir = Get-location

$Timestamp = get-date -format d

$xlsx = [String] $CurrentLocation   "\MyNewExcelStuff-"   $Timestamp   ".xlsx"

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $true
$excel.DisplayAlerts = $False
$workbook = $excel.Workbooks.add()
$sheet1 = $workbook.worksheets.Item(1)
$sheet1.name = "Stuff"

$Sheet1.Cells.Item(1,1) = "Reporting Stack Stuff"
$title = $Sheet1.Range("A1:K1")
$title.Select() 
$title.MergeCells = $true
$title.VerticalAlignment = -4108           # Centre (vertically) heading
$title.HorizontalAlignment = -4108         # Centre (horizontally) heading
$Title.Interior.ColorIndex = 0

$Excel.ActiveWorkbook.SaveAs($xlsx, $xlFixedFormat)
Start-Sleep -s 2
$Excel.Quit()
$Excel = $Null

CodePudding user response：

You should use $workbook.Close($false).