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 work perfectly.

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

In SSMS we have 2 case :

1. I run the SSIS package that call the ps1 file with the powershell script : the job run over and over and never stop and nothing happen
2. I run directly the powershell in the job : the job run over and over and never stop BUT the macro started, because it create a folder.

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

SSMS is actually host 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.

I’m not afraid of technical solution like powershell, VBA, VBScript or C#. I will accept any solution if it works 🙂 ! (There is no constraints on this point)