VBA宏本地运行正常，SSMS SQL代理作业执行无响应

我有一个VBA宏，功能是创建文件夹、复制指定Excel文件到该文件夹并修改文件。该宏在以下场景运行正常：

• 本地直接运行
• 通过PowerShell脚本本地运行
• 调用该PowerShell脚本的SSIS包本地运行

但通过SSMS的SQL代理作业执行时，作业持续加载无法结束，具体表现：

1. 运行调用ps1文件的SSIS包：作业一直处于运行状态但无任何操作
2. 直接运行PowerShell脚本：作业持续运行，但宏已成功创建目标文件夹

无任何错误提示，且在SSMS所在服务器本地直接运行宏也正常，权限配置及Excel版本均无问题。尝试将逻辑改写为VBScript或C#代码在SSIS中运行均未成功，推测服务器缺少对应依赖库。

附VBA代码：

Public Fichier      As String, Chemin As String
Public Database     As Workbook

Sub macro9()
    Application.ScreenUpdating = FALSE
    Application.DisplayAlerts = FALSE
    Chemin = "somepath"
    
    Fichier = Dir(Chemin & "*MMM*.xlsx")
    'Start of creation of the folder
    If Fichier = "" Then
        End
    End If
    On Error Resume Next
    Chemin_final = Chemin & "Fichiers mis en forme\"
    MkDir Chemin_final
    
    On Error GoTo 0
    'End of creation of the folder
    
    Fichier = Dir(Chemin & "*MMM*.xlsx")
    Do While Fichier <> ""
        
        Set Database = Workbooks.Open(Filename:=Chemin & Fichier, CorruptLoad:=XlCorruptLoad.xlRepairFile)
        Database.SaveAs Filename:=Chemin_final & Database.Name, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
        Database.Close
        Fichier = Dir
        
    Loop
    
End Sub

SQL代理作业执行卡住的核心原因是服务运行上下文与本地用户环境的差异：

• SQL代理默认用本地系统账户运行，该账户无交互式桌面权限，而Excel作为GUI程序，部分操作在无交互环境下会挂起
• VBA代码存在资源未正确释放、错误处理不严谨的问题，在服务环境下会导致进程无法正常退出
• PowerShell调用Excel时未确保进程彻底终止，残留的Excel进程会让作业持续显示运行状态

1. 修复VBA代码的潜在问题 #

（1）完善错误处理与资源释放 #

替换强制终止语句，添加错误捕获逻辑，确保无论是否出错都恢复Excel默认设置，避免资源泄漏：

Public Fichier      As String, Chemin As String
Public Database     As Workbook

Sub macro9()
    Dim Chemin_final As String
    ' 保存Excel初始设置
    Dim originalScreenUpdating As Boolean
    Dim originalDisplayAlerts As Boolean
    originalScreenUpdating = Application.ScreenUpdating
    originalDisplayAlerts = Application.DisplayAlerts
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    On Error GoTo Cleanup
    
    Chemin = "somepath"
    
    Fichier = Dir(Chemin & "*MMM*.xlsx")
    If Fichier = "" Then
        Exit Sub ' 替换End，正常退出程序
    End If
    
    On Error Resume Next
    Chemin_final = Chemin & "Fichiers mis en forme\"
    MkDir Chemin_final
    On Error GoTo Cleanup ' 恢复全局错误捕获
    
    Fichier = Dir(Chemin & "*MMM*.xlsx")
    Do While Fichier <> ""
        On Error Resume Next ' 单个文件出错时跳过，避免整个循环卡住
        Set Database = Workbooks.Open(Filename:=Chemin & Fichier, CorruptLoad:=XlCorruptLoad.xlRepairFile)
        If Not Database Is Nothing Then
            Database.SaveAs Filename:=Chemin_final & Database.Name, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
            Database.Close SaveChanges:=False
            Set Database = Nothing
        End If
        On Error GoTo Cleanup
        Fichier = Dir
    Loop
    
Cleanup:
    ' 恢复Excel默认设置
    Application.ScreenUpdating = originalScreenUpdating
    Application.DisplayAlerts = originalDisplayAlerts
    ' 确保释放Workbook对象
    If Not Database Is Nothing Then
        Database.Close SaveChanges:=False
        Set Database = Nothing
    End If
End Sub

（2）明确变量类型 #

添加Dim Chemin_final As String声明，避免隐式类型转换导致的未知问题。

2. 调整SQL代理作业的运行账户 #

• 将SQL代理作业的运行账户改为具备本地登录权限、目标路径读写权限、Excel执行权限的域账户或本地账户（避免使用本地系统账户）
• 若必须使用服务账户，可临时在Windows服务设置中为SQL Server Agent启用「允许服务与桌面交互」（此操作存在安全风险，仅作排查用）

3. 优化PowerShell脚本调用逻辑 #

如果通过PowerShell调用VBA，需确保脚本彻底释放Excel进程，避免残留：

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
$excel.ScreenUpdating = $false
$excel.DisplayAlerts = $false

# 调用VBA宏
$workbook = $excel.Workbooks.Open("C:\Path\To\Your\MacroFile.xlsm")
$excel.Run("macro9")
$workbook.Close($false)
$excel.Quit()

# 强制释放COM对象，避免残留进程
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
Remove-Variable excel, workbook

同时添加日志输出，方便定位卡住环节：

Write-Output "[$(Get-Date)] 开始创建文件夹..." | Out-File "C:\Logs\macro_log.txt" -Append
Write-Output "[$(Get-Date)] 文件夹创建完成，开始处理Excel文件..." | Out-File "C:\Logs\macro_log.txt" -Append

4. 替代方案：使用无Office依赖的Excel处理库 #

若服务器缺少Office Interop库，可改用开源库EPPlus（适用于.NET/C#）直接处理Excel，无需安装Microsoft Office：

• 在SSIS的Script Task中引用EPPlus库
• 实现复制文件、修改Excel的逻辑示例（C#）：

using OfficeOpenXml;
using System.IO;

public void Main()
{
    string sourcePath = "somepath";
    string targetFolder = Path.Combine(sourcePath, "Fichiers mis en forme");
    Directory.CreateDirectory(targetFolder);
    
    foreach (string filePath in Directory.GetFiles(sourcePath, "*MMM*.xlsx"))
    {
        string fileName = Path.GetFileName(filePath);
        string targetPath = Path.Combine(targetFolder, fileName);
        
        File.Copy(filePath, targetPath, overwrite: true);
        
        // 修改Excel示例：设置第一个工作表A1单元格值
        using (ExcelPackage package = new ExcelPackage(new FileInfo(targetPath)))
        {
            ExcelWorksheet worksheet = package.Workbook.Worksheets[0];
            worksheet.Cells["A1"].Value = "Modified via EPPlus";
            package.Save();
        }
    }
    
    Dts.TaskResult = (int)ScriptResults.Success;
}

注意：使用EPPlus需确保服务器已安装对应版本库，或在SSIS包中嵌入依赖文件。

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