Powershell实现.XLS转.XLSX脚本报语法错误求排查修复

• 1. try语句结构不完整：PowerShell强制要求try块必须搭配至少一个catch或finally块，你的代码中try块开启后没有配套的异常处理块，直接结束了语法结构。
• 2. 花括号闭合缺失：代码中ForEach-Object的脚本块、try块都缺少对应的闭合}，括号数量不匹配导致语法解析失败。
• 3. 路径变量未定义：Get-ChildItem调用时使用了不存在的变量$folderpath，你预先定义的存储xls的路径变量是$downloadfolder，变量名不匹配会导致无法读取目标文件。
• 4. 垃圾回收代码位置错误：你将COM对象回收的逻辑写在了未闭合的try块内部，既不符合语法要求，也无法正确释放Excel进程资源。

# set folders
$downloadfolder = "C:\Users\Test"
#$downloadfolder = "folder that gets the .xls files"
$uploadfolder = "C:\Users\Test\Upload"
#$uploadfolder = "folder that uploads the .xlsx files"
$backupfolder = "C:\Users\Test\Backup"

# 预先判断文件夹是否存在，不存在则创建
if (-not (Test-Path $backupfolder)) { New-Item -ItemType Directory -Path $backupfolder | Out-Null }
if (-not (Test-Path $uploadfolder)) { New-Item -ItemType Directory -Path $uploadfolder | Out-Null }

#open and convert xls to xlsx
Add-Type -AssemblyName Microsoft.Office.Interop.Excel
$xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlOpenXMLWorkbook
write-host $xlFixedFormat
$excel = New-Object -ComObject excel.application
$excel.visible = $true
$filetype ="*xls"
# 替换未定义的$folderpath为$downloadfolder
Get-ChildItem -Path $downloadfolder -Include $filetype -recurse | 
ForEach-Object {
    $workbook = $null
    try {
        $xlsfilename = $_.fullname
        #copy file to backup folder
        Copy-Item -Path $xlsfilename -Destination $backupfolder -Force
        # open the xls
        Write-Output "Converting $xlsfilename"
        $workbook = $excel.workbooks.open($xlsfilename)
        # 优化xlsx文件名生成逻辑，避免直接加x出错
        $xlsxfilename = Join-Path $_.DirectoryName ($_.BaseName + ".xlsx")
        $workbook.saveas($xlsxfilename, $xlFixedFormat)
        $workbook.close()
        #remove old file
        Write-Output "delete & move file(s)"
        Remove-Item -Path $xlsfilename -Force
        Move-Item -Path $xlsxfilename -Destination $uploadfolder -Force
    }
    # 补全catch块捕获转换异常
    catch {
        Write-Error "转换文件 $xlsfilename 失败：$_"
        # 出现异常时关闭已打开的工作簿
        if ($workbook) { $workbook.close() }
    }
}
# 垃圾回收逻辑移到循环外，正确释放Excel进程
[gc]::collect()
[gc]::WaitForPendingFinalizers()
# close excel
$excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
$excel = $null

• 新增了文件夹存在性判断，避免目标文件夹不存在时复制/移动文件报错
• 优化了xlsx文件名生成逻辑，避免原文件名中间包含xls字符时命名出错
• 新增了COM对象显式释放逻辑，避免Excel进程残留在后台
• 新增了异常场景下的工作簿关闭逻辑，避免文件被锁定

内容的提问来源于stack exchange，提问作者excedentH7