VBA macro works in local but not in SSMS SQL agent job

Question

I want to run a VBA macro that create a folder then copy Excel file in this folder then modify them.

I run this macro perfectly on local (I press the "play" button in the macro editor). I tried to run it by PowerShell script directly on my pc and it works well too. I try to create a package SSIS that call a ps1 file with the previous PowerShell script and when I run it in local it works perfectly.

BUT, when I try to do it with a SQL Agent Job in SSMS the job step just loads and loads and loads but never ends (and doesn't work).

In SSMS we have 2 case:

1. I run the SSIS package that calls the ps1 file with the PowerShell script : the job runs over and over and never stops and nothing happens
2. I run directly the PowerShell in the job : the job runs over and over and never stops BUT the macro started, because it creates a folder.

Unfortunately there is no error message and nothing, and when I try to run something in local everything is good and everything is working perfectly fine.

SSMS is actually hosted in another server but I try to run the macro DIRECTLY in local on the SSMS server and guess what? It works. So it's not even a problem of Excel version or something weird, the code works. So how this could be even possible?

In term of rights normally we good, the server as all rights possible and again I test the macro on my pc AND on the server and it works perfectly.

Here's the VBA code:

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

PS: I tried to recode this macro in VBScript to run it directly on a SSIS Package and it doesn't work but I thinks it's because I don't have the good library on the SSMS server. I also tried to run it with C# code and it's the same result.

Answer 1

When the code runs under SQL Server Agent it will run under whatever account the service is configured to use & automating office is not supported/recommended at all when run under a service account.

If you need to modify the contents of the workbooks consider using OpenXml or OLEDB via C#/Powershell.