PowerShell脚本处理XLSM宏启用工作簿时部分客户端异常的排查求助

算力阿峰

2026-4-20

各位好，我遇到了一个棘手的问题，想请大家帮忙分析下：

我有20位远程办公的用户，每天需要填写Excel工作日志。之前我们提供只读的XLSX模板，用PowerShell脚本帮他们自动生成每日日志——脚本会打开模板、填充基础信息、另存为正确命名的文件到个人文档目录，最后自动打开给用户。这个方案用了好几年都没出过问题。

一周前我们把日志文件改成了宏启用的XLSM格式：

我把旧的XLSX文件另存为XLSM格式，确保内部结构正确
脚本里把所有扩展名从.xlsx改成了.xlsm
远程测试了几台用户机器，一切正常

但20个用户里有1个出现了问题：脚本执行到SaveAs步骤时，报出文件类型错误。查资料后我添加了以下代码来明确指定文件类型：

Add-Type -AssemblyName Microsoft.Office.Interop.Excel
$xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlOpenXMLWorkbookMacroEnabled
# ...
$workbook.saveas($filename,$xlFixedFormat)

这解决了第一个用户的问题，但又导致另一位用户的机器出现新报错：

PS C:\Users\MyUser\OneDrive\Setup> .\DailyCopy1.ps1

Add-Type : Could not load file or assembly 'Microsoft.Office.Interop.Excel, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c' or one of its dependencies. The system cannot find the file specified.

At C:\Users\MyUser\OneDrive\Setup\DailyCopy1.ps1:2 char:1

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

+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

+ CategoryInfo          : NotSpecified: (:) [Add-Type], FileNotFoundException

+ FullyQualifiedErrorId : System.IO.FileNotFoundException,Microsoft.PowerShell.Commands.AddTypeCommand

Unable to get the SaveAs property of the Workbook class

At C:\Users\MyUser\OneDrive\Setup\DailyCopy1.ps1:45 char:10

+          throw $_.Exception.Message

+          ~~~~~~~~~~~~~~~~~~~~~~~~~~

+ CategoryInfo          : OperationStopped: (Unable to get t... Workbook class:String) [], RuntimeException

+ FullyQualifiedErrorId : Unable to get the SaveAs property of the Workbook class

为了解决程序集加载失败的问题，我尝试直接指定DLL的路径：

Add-Type -Path $env:WINDIR\assembly\GAC_MSIL\Microsoft.Office.Interop.Excel\15.0.0.0__71e9bce111e9429c\Microsoft.Office.Interop.Excel.dll

这个命令能成功执行，但SaveAs步骤还是会报错——有意思的是，目标文件其实已经正确生成在目录里，内容也没问题，但脚本会中断，不会自动打开文件，而且Excel进程会一直留在系统内存里。

所有用户的软硬件环境都是完全一致的：

操作系统：Win10 64位（已安装最新更新）
Office版本：Microsoft 365 Excel 32位（版本2311 Build 16.0.17029.20108）

我已经尝试过的排查操作：

运行sfc /scannow修复系统文件
执行Office在线修复
使用.NET Framework修复工具
多次重启机器
在其他同环境机器测试，脚本运行正常

以下是当前的完整脚本：

Add-Type -AssemblyName System.Windows.Forms
Add-Type -AssemblyName Microsoft.Office.Interop.Excel
Add-Type -Path $env:WINDIR\assembly\GAC_MSIL\Microsoft.Office.Interop.Excel\15.0.0.0__71e9bce111e9429c\Microsoft.Office.Interop.Excel.dll

$xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlOpenXMLWorkbookMacroEnabled

$EMSDate = Get-Date -Format "yyyy-MM-dd"
$filecheck = "$env:UserProfile\OneDrive\Vessel Templates\EMSV5.xlsm"

# Check if template is XLSM or not
if (!(Test-Path $filecheck)) {
    $filename = "$env:UserProfile\OneDrive\WorkingFolder\" + $EMSDate + " Daily Forms.xlsx"
    $template = "$env:UserProfile\OneDrive\Vessel Templates\EMSV5.xlsx"
}
else {
    $filename = "$env:UserProfile\OneDrive\WorkingFolder\" + $EMSDate + " Daily Forms.xlsm"
    $template = "$env:UserProfile\OneDrive\Vessel Templates\EMSV5.xlsm"
}

$EMSEmpList = "$env:UserProfile\OneDrive\Vessel Templates\Data\EMSEmpList.csv"
$EMSStrapping = "$env:UserProfile\OneDrive\Vessel Templates\Data\StrappingTables.csv"
$EMSBoatName = $env:emslocaluser

# If the file does not exist, create it.
if (-not(Test-Path -Path $filename -PathType Leaf)) {
    try {
        $excel = new-object -comobject Excel.Application
        $excel.DisplayAlerts = $false
        $excel.ScreenUpdating = $false
        $excel.Visible = $false
        $excel.UserControl = $false
        $excel.Interactive = $false

        $workbook = $excel.workbooks.add($template)

        $s1 = $workbook.sheets | Where-Object {$_.name -eq 'TABLES'}
        $s1.range("A2:A2").cells = $EMSDate
        $s1.range("A3:A3").cells = $EMSBoatName
        $s1.range("A5:A5").cells = $EMSEmpList
        $s1.range("A6:A6").cells = $EMSStrapping

        $workbook.saveas($filename,$xlFixedFormat)

        $excel.quit()
        Start-Sleep -Seconds 1
        [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
        Start-Sleep -Seconds 2
        Remove-Variable excel
        Start-Sleep -Seconds 2
        Invoke-Item $filename
    }
    catch {
        throw $_.Exception.Message
    }
}
# If the file already exists, show the message and do nothing.
else {
    $ButtonType = [System.Windows.Forms.MessageBoxButtons]::OK
    $MessageIcon = [System.Windows.Forms.MessageBoxIcon]::Error
    $MessageBody = "Cannot create new Daily Log because one already exists in the working folder with todays date."
    $MessageTitle = "Duplicate"
    [System.Windows.Forms.MessageBox]::Show($MessageBody,$MessageTitle,$ButtonType,$MessageIcon)
}

有没有大佬能帮我分析下为什么个别机器会出现这种奇怪的情况？感激不尽！

备注：内容来源于stack exchange，提问作者Davschm