Powershell Excel SaveAs需要确认

pxy2qtax 于 2024-01-08 发布在 Shell

关注(0)|答案(3)|浏览(227)

我使用下面的脚本来转换一堆xls文件到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()

字符串
它曾经完美地运行在VM上。不幸的是，随着更改文件夹路径，我意识到有弹出窗口来确认保存之前没有出现，脚本卡住了。任何简单的更正，可以防止该错误？

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

型

powershell

来源：https://stackoverflow.com/questions/70267748/powershell-excel-saveas-requires-confirmation

3条答案

按热度按时间

wfypjpf41#

下面是一个使用PowerShell保存Excel文件时如何设置路径的示例。我使用Get-Location小程序、Get-Date小程序和文件名的组合来设置路径，文件名存储在字符串变量中，供保存脚本时使用。

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

字符串

展开查看全部

赞(0）回复(0）举报 2024-01-08

hmae6n7t2#

try $workbook.SaveCopyAs（$path）
当我升级到Office 2016时，我在Azure DevOps管道中的Powershell脚本中出现了相同的错误消息。
升级后，它停止工作，但脚本仍然在同一升级的构建机器上手动工作正常。
1.最后我试着保存副本。
1.我还在代码中添加了以下行
$excel.visible = $false
$excel.DisplayAlerts=$false
1.我还创建了以下文件夹：