Workbooks.Open method!

Hi there,

here is a small context:
I created a worksheet for project manager to fill bi-weekly with their project's status. One worksheet per project. So each project manager have 1 excel file with many worksheets since they all have more than one project. Each worksheet as a unique name (project's id).

I created a workbook for the project management office (PMO). In that workbook, I have different worksheets and one of them lists all projects with some of their info. For that worksheet to work, I need to have a copy of all the worksheets.

I created a VBA macro that will open all excel file in the same folder and loop through all worksheet and copy them into the PMO workbook.

It was working well until recently and I just can't find why it stopped working.

Basically, the VBA macro loops through all excel files in the current folder. Open them one by one and loops through all worksheet except the one titled "Listes" and copy them into the main workbook. Pretty straight forward.

The thing is that now, when I try to open a workbook using Workbooks.Open(path_to_the_workbook), it opens it, make it active and that's it. It never reaches the next line of code in my VBA.

Any idea what's happening?

Here is the code:

Public Sub MettreAJour()

    On Error GoTo ErreurMettreAJour
    
    Dim excelFilesCollection As New Collection  ' Collection des fichiers Excel des chargés de projet
    Dim chargeProjetWorkbook As Workbook        ' Fichier Excel de la feuille de temps
    Dim excelFile As File                       ' Variable de bouclage
    Dim ficheProjet As Worksheet                ' Variable pour le bouclage des fiches projet
    
    ' Make sure archives folders exist
    If Len(Dir(Application.ThisWorkbook.Path & "\Archives", vbDirectory)) < 1 Then
        MkDir Application.ThisWorkbook.Path & "\Archives"
    End If
    
    If Len(Dir(Application.ThisWorkbook.Path & "\Archives\Donnees", vbDirectory)) < 1 Then
        MkDir Application.ThisWorkbook.Path & "\Archives\Donnees"
    End If
    
    ' Backup this file in case of a crash in the middle of the import
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveCopyAs Filename:=Application.ActiveWorkbook.Path & "\Archives\Donnees\Backup-" & CStr(Format(Now, "YYYYMMDD-HhNn")) & ".xlsm"
    Application.DisplayAlerts = True

    ' Get a list of all excel file in the current folder
    GetListeDesFichiersExcel excelFilesCollection
    
    ' Loop through each excel file
    For Each excelFile In excelFilesCollection
    
        If (excelFile.Path <> ThisWorkbook.FullName) Then
            ' Open the workbook with Workbooks.Open as oppose to a standalone Excel object since both workbook
            ' need to be opened in the same process to allow copying of worksheet
            Set chargeProjetWorkbook = Workbooks.Open(ThisWorkbook.Path & "\" & excelFile.Name)
            
            ' Delete named range to prevent duplicate named range, which are useless anyway in this workbook
            SupprimeNamedRange chargeProjetWorkbook
            
            ' Loop in each of the worksheet
            For Each ficheProjet In chargeProjetWorkbook.Worksheets
                
                ' Import all but "Listes" worksheet
                If (ficheProjet.Name <> "Listes") Then
                    ' If the worksheet already exists (from a previous import), delete it so we can re-import it with its fresh data
                    If IsWorksheetExists(ThisWorkbook, ficheProjet.Name) Then
                        Application.DisplayAlerts = False
                        ThisWorkbook.Worksheets(ficheProjet.Name).Delete
                        Application.DisplayAlerts = True
                    End If
                    
                    ' Remove validation of all cells
                    ficheProjet.Cells.Validation.Delete
                    
                    ' Copy the worksheet at the appropriate place
                    ' The EmplacementFichesProjetOrdreAlphabetique function returns the right index where the worksheet should be inserted
                    ficheProjet.Copy After:=ThisWorkbook.Worksheets(EmplacementFichesProjetOrdreAlphabetique(ficheProjet.Name))
                    
                    ' Make the worksheet read-only
                    ThisWorkbook.Worksheets(ficheProjet.Name).Protect Password:="test"
                End If
                
            Next
            
            ' Close the workbook
            chargeProjetWorkbook.Close SaveChanges:=False
            Set chargeProjetWorkbook = Nothing
            
            ' Copy the original workbook in the archive
            Name ThisWorkbook.Path & "\" & excelFile.Name As ThisWorkbook.Path & "\Archives\" & CStr(Format(Now, "YYYYMMDD-HhNn")) & "-" & excelFile.Name
            
        End If
    Next
   
    Set chargeProjetWorkbook = Nothing
    Set excelFilesCollection = Nothing
    
    
    GoTo FinMettreAJour 'Skip Error Handling
    
ErreurMettreAJour:           ' Error Handling
    MsgBox Err.Description, , Err.Number
    
FinMettreAJour:          ' Fin de la procédure!
    ThisWorkbook.Worksheets(1).Activate
    MsgBox "L'import des fiches projet est terminée!", vbOKOnly + vbInformation, "Import terminée"
    
End Sub