批量移除Excel文件宏：PowerShell脚本SaveAs弹窗问题求助

问题描述 #

我正在企业网络内执行启用宏的文件清理流程，用PowerShell结合Excel COM对象处理，普通宏文件能正常处理，但带VB Project的Excel文件调用SaveAs方法时会弹出提示框，卡住批量处理（要处理数千个文件）。目标是移除宏、保留数据用于存档，手动另存为xlsx可以实现，但复制内容会丢失原文件关联，不符合需求。

当前使用的PowerShell函数：

function Process-ExcelFile {
    param (
        [string]$FullPath,
        [string]$DestinationFile
    )

    $excel = New-Object -ComObject Excel.Application
    $excel.Visible = $false
    $excel.DisplayAlerts = 0 # Disable display alerts 
    #$excel.DisplayAlerts = $false # Disable display alerts works the same as 0
    $excel.AlertBeforeOverwriting = 0
    $workbook = $excel.Workbooks.Open($FullPath)
    # Save the file as non-macro enabled workbook with appropriate extension
    $destinationFile = [System.IO.Path]::ChangeExtension($destinationFile, "xlsx")
    $workbook.SaveAs($destinationFile, 51) # 51 = Excel File Format for non-macro workbook
    $workbook.Close($false) # Close the workbook without saving changes

    $excel.Quit()
}

尝试过移除VB Project但没成功抑制弹窗，弹窗会阻塞SaveAs进程，无法用代码响应。

可行解决方案 #

方案1：调整SaveAs参数并强化警告禁用 #

除了设置DisplayAlerts，还要在打开工作簿和保存时补充参数，避免Excel触发额外提示：

function Process-ExcelFile {
    param (
        [string]$FullPath,
        [string]$DestinationFile
    )

    $excel = New-Object -ComObject Excel.Application
    $excel.Visible = $false
    $excel.DisplayAlerts = $false
    $excel.AlertBeforeOverwriting = $false
    # 打开时禁用更新链接、只读提示
    $workbook = $excel.Workbooks.Open($FullPath, $false, $false, 5, "", "", $true)
    $destinationFile = [System.IO.Path]::ChangeExtension($destinationFile, "xlsx")
    # SaveAs指定冲突处理参数，覆盖潜在提示
    $workbook.SaveAs($destinationFile, 51, "", "", $false, $false, 1, 2)
    $workbook.Close($false)
    
    # 清理COM对象避免残留
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook) | Out-Null
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
    [GC]::Collect()
    [GC]::WaitForPendingFinalizers()
}

参数说明：

• Workbooks.Open第7个参数设为$true，跳过只读推荐提示
• SaveAs第7个参数设为1（独占访问模式），第8个参数设为2（本地会话优先），解决文件冲突类提示

方案2：先移除VB Project再保存 #

若方案1仍有弹窗，可彻底删除工作簿内的VB项目内容后再保存：

function Process-ExcelFile {
    param (
        [string]$FullPath,
        [string]$DestinationFile
    )

    $excel = New-Object -ComObject Excel.Application
    $excel.Visible = $false
    $excel.DisplayAlerts = $false
    $excel.AlertBeforeOverwriting = $false
    $workbook = $excel.Workbooks.Open($FullPath, $false, $false, 5, "", "", $true)
    
    # 删除VB项目所有组件和引用
    if ($workbook.VBProject) {
        $vbProject = $workbook.VBProject
        # 倒序删除模块，避免索引混乱
        for ($i = $vbProject.VBComponents.Count; $i -ge 1; $i--) {
            $vbProject.VBComponents.Remove($vbProject.VBComponents.Item($i))
        }
        # 删除外部引用
        for ($i = $vbProject.References.Count; $i -ge 1; $i--) {
            $vbProject.References.Remove($vbProject.References.Item($i))
        }
    }

    $destinationFile = [System.IO.Path]::ChangeExtension($destinationFile, "xlsx")
    $workbook.SaveAs($destinationFile, 51, "", "", $false, $false, 1, 2)
    $workbook.Close($false)
    
    # 清理COM对象
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook) | Out-Null
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
    [GC]::Collect()
    [GC]::WaitForPendingFinalizers()
}

注意：此方法需要在Excel信任中心启用信任对VBA项目对象模型的访问，若企业环境限制该设置，优先用方案1。

方案3：使用Open XML SDK（无COM依赖） #

如果COM对象的弹窗问题始终无法解决，改用Open XML SDK完全绕过Excel界面，适合批量处理：
先安装SDK模块：

Install-Module -Name DocumentFormat.OpenXml -Scope CurrentUser

处理函数：

function Convert-XlsmToXlsx {
    param (
        [string]$SourcePath,
        [string]$DestinationPath
    )

    # 复制原文件结构到目标路径
    Copy-Item -Path $SourcePath -Destination $DestinationPath -Force
    $DestinationPath = [System.IO.Path]::ChangeExtension($DestinationPath, "xlsx")
    
    # 移除宏相关部件并修改文档类型
    $package = [DocumentFormat.OpenXml.Packaging.SpreadsheetDocument]::Open($DestinationPath, $true)
    $vbParts = $package.GetPartsByContentType("application/vnd.ms-office.vbaProject")
    foreach ($part in $vbParts) {
        $package.DeletePart($part)
    }
    $package.ChangeDocumentType([DocumentFormat.OpenXml.Packaging.SpreadsheetDocumentType]::Workbook)
    $package.Close()
}

此方法无需安装Excel，无弹窗问题，能保留原文件大部分格式和关联信息，适合服务器批量处理场景。

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