SOLVED

Select all files in a folder automatically

%3CLINGO-SUB%20id%3D%22lingo-sub-2500772%22%20slang%3D%22en-US%22%3ESelect%20all%20files%20in%20a%20folder%20automatically%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2500772%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20code%20for%26nbsp%3Bfussing%20with%20several%20sheets%20in%20the%20same%20folder%20by%26nbsp%3Bbut%20you%20have%20to%20select%20the%20files%20manually%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22minhhai91_0-1625042596352.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F292517i1DEFC034CBFE7969%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22minhhai91_0-1625042596352.png%22%20alt%3D%22minhhai91_0-1625042596352.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Ethis%20is%20the%20code%20I%20actually%20have%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22minhhai91_1-1625042670064.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F292518i8CE51838600A9741%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22minhhai91_1-1625042670064.png%22%20alt%3D%22minhhai91_1-1625042670064.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThis%20is%20the%20code%20i%20want%20to%20keep%20and%20I%20would%20like%20to%20add%26nbsp%3Bautomatic%20selection%20of%20all%20files%20in%20a%20folder%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFr%C3%A9d%C3%A9ric%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2500772%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2501198%22%20slang%3D%22en-US%22%3ERe%3A%20Select%20all%20files%20in%20a%20folder%20automatically%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2501198%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1087070%22%20target%3D%22_blank%22%3E%40minhhai91%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20you%20go%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20Fusionner()%0A%20%20%20%20Dim%20wbFusion%20As%20Workbook%0A%20%20%20%20Dim%20wbCible%20As%20Workbook%0A%20%20%20%20Dim%20shCible%20As%20Worksheet%0A%20%20%20%20Dim%20stDossier%20As%20String%0A%20%20%20%20Dim%20stFichier%20As%20String%0A%20%20%20%20%0A%20%20%20%20With%20Application.FileDialog(4)%20'%20msoFileDialogFolderPicker%0A%20%20%20%20%20%20%20%20.InitialFileName%20%3D%20ThisWorkbook.Path%0A%20%20%20%20%20%20%20%20If%20.Show%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20stDossier%20%3D%20.SelectedItems(1)%20%26amp%3B%20Application.PathSeparator%0A%20%20%20%20%20%20%20%20Else%0A%20%20%20%20%20%20%20%20%20%20%20%20Beep%0A%20%20%20%20%20%20%20%20%20%20%20%20Exit%20Sub%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20End%20With%0A%20%20%20%20%0A%20%20%20%20Set%20wbFusion%20%3D%20ThisWorkbook%0A%20%20%20%20%0A%20%20%20%20stFichier%20%3D%20Dir(stDossier%20%26amp%3B%20%22*.xls*%22)%0A%20%20%20%20Do%20While%20stFichier%20%26lt%3B%26gt%3B%20%22%22%0A%20%20%20%20%20%20%20%20Set%20wbCible%20%3D%20Workbooks.Open(stDossier%20%26amp%3B%20stFichier)%0A%20%20%20%20%20%20%20%20For%20Each%20shCible%20In%20wbCible.Worksheets%0A%20%20%20%20%20%20%20%20%20%20%20%20shCible.Copy%20After%3A%3DwbFusion.Worksheets(wbFusion.Worksheets.Count)%0A%20%20%20%20%20%20%20%20Next%20shCible%0A%20%20%20%20%20%20%20%20wbCible.Close%20SaveChanges%3A%3DFalse%0A%20%20%20%20%20%20%20%20stFichier%20%3D%20Dir%0A%20%20%20%20Loop%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2501555%22%20slang%3D%22en-US%22%3ERe%3A%20Select%20all%20files%20in%20a%20folder%20automatically%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2501555%22%20slang%3D%22en-US%22%3EThanks%20for%20your%20anwser.%3CBR%20%2F%3E%3CBR%20%2F%3Ehow%20can%20I%20add%20a%20path%20so%20I%20don't%20have%20to%20manually%20select%20the%20folder%20%3F%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello,

 

I have a code for fussing with several sheets in the same folder by but you have to select the files manually:

minhhai91_0-1625042596352.png

this is the code I actually have

 

minhhai91_1-1625042670064.png

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

4 Replies

@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
Thanks for your anwser.

how can I add a path so I don't have to manually select the folder ?
best response confirmed by minhhai91 (Occasional Contributor)
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 \

Thank you very much, it work.