Hello, I have a code for fussing with several sheets in the same folder by but you have to select the files manually:this is the code I actually have This is the code i want to keep and I would like to add automatic selection of all files in a folder Thanks in advance. Frédéric

minhhai91 Replace With Application.FileDialog(4) ' msoFileDialogFolderPicker .InitialFileName = ThisWorkbook.Path If .Show Then stDossier = .SelectedItems(1) & Application.PathSeparator Else Beep Exit Sub End If End With with a line that specifies the path: stDossier = "C:\MyFolder\MySubfolder\" The path must end in a backslash \

Select all files in a folder automatically

With Application.FileDialog(4) ' msoFileDialogFolderPicker .InitialFileName = ThisWorkbook.Path If .Show Then stDossier = .SelectedItems(1) & Application.PathSeparator Else Beep Exit Sub End If End With

4 Replies

HansVogelaar

MVP

Jun 30, 2021

minhhai91

Here you go:

Sub Fusionner()
    Dim wbFusion As Workbook
    Dim wbCible As Workbook
    Dim shCible As Worksheet
    Dim stDossier As String
    Dim stFichier As String
    
    With Application.FileDialog(4) ' msoFileDialogFolderPicker
        .InitialFileName = ThisWorkbook.Path
        If .Show Then
            stDossier = .SelectedItems(1) & Application.PathSeparator
        Else
            Beep
            Exit Sub
        End If
    End With
    
    Set wbFusion = ThisWorkbook
    
    stFichier = Dir(stDossier & "*.xls*")
    Do While stFichier <> ""
        Set wbCible = Workbooks.Open(stDossier & stFichier)
        For Each shCible In wbCible.Worksheets
            shCible.Copy After:=wbFusion.Worksheets(wbFusion.Worksheets.Count)
        Next shCible
        wbCible.Close SaveChanges:=False
        stFichier = Dir
    Loop
End Sub

minhhai91

Copper Contributor

Jun 30, 2021

Thanks for your anwser.

how can I add a path so I don't have to manually select the folder ?

HansVogelaar

MVP

Jun 30, 2021

minhhai91

Replace

    With Application.FileDialog(4) ' msoFileDialogFolderPicker
        .InitialFileName = ThisWorkbook.Path
        If .Show Then
            stDossier = .SelectedItems(1) & Application.PathSeparator
        Else
            Beep
            Exit Sub
        End If
    End With

with a line that specifies the path:

    stDossier = "C:\MyFolder\MySubfolder\"

The path must end in a backslash \

Forum Discussion

Select all files in a folder automatically

4 Replies

Resources