Select all files in a folder automatically

[minhhai91]

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

[#2]

@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

[#3]

Thanks for your anwser.

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

[#4 · Accepted solution]

@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 \

[#5]

Thank you very much, it work.