How do I change my code to pick all the files within a folder instead of manually selecting them?

Question

I am running a macro to combine multiple excel workbooks into one sheet. But the way it works now is that it opens a file dialogue and lets me select the required files within a folder. How can I make it so that I just select the folder and it automatically selects all the files within the folder without me having to do it manually?

Any help would be greatly appreciated. Thank you!

Here's my code

Dim CurrentBook As Workbook
Dim WS As Worksheet
Set WS = ThisWorkbook.Sheets("Sheet1")
Dim IndvFiles As FileDialog
Dim FileIdx As Long
Dim i As Integer, x As Integer

Set IndvFiles = Application.FileDialog(msoFileDialogOpen)
With IndvFiles
    .AllowMultiSelect = True
    .Title = "Multi-select target data files:"
    .ButtonName = ""
    .Filters.Clear
    .Filters.Add ".xlsx files", "*.xls*"
    .Show
End With

Application.DisplayAlerts = False
Application.ScreenUpdating = False

For FileIdx = 1 To IndvFiles.SelectedItems.Count
    Set CurrentBook = Workbooks.Open(IndvFiles.SelectedItems(FileIdx))
    For Each Sheet In CurrentBook.Sheets
        Dim LRow1 As Long
        LRow1 = WS.Range("A" & WS.Rows.Count).End(xlUp).Row

Look on here, there are Q&A with how to list files from a directory then use that list to drive a loop. — Solar Mike, May 05 '22 at 07:40
https://stackoverflow.com/questions/10380312/loop-through-files-in-a-folder-using-vba — braX, May 05 '22 at 07:51
The [GetFiles](https://github.com/cristianbuse/VBA-FileTools/blob/master/src/LibFileTools.bas#L587) method picks up all files in a folder and you have the ability to pick up subfolders as well. — Cristian Buse, May 05 '22 at 08:08

Ron Rosenfeld · Answer 1 · 2022-05-05T12:31:07.130

I would suggest using the FileSystemObject instead.

In the code below, I'm using early binding (reference set to Microsoft Scripting Runtime) but you could use late binding instead, if you were going to distribute this.

'Set reference to Microsoft Scripting Runtime
Option Explicit
Sub getAllFilesInFolder()
    Dim sPath 'As String
    Dim FD As FileDialog
    Dim FSO As FileSystemObject
    Dim FIs As Files, FI As File
    Dim FO As Folder
    
'Get path
    Set FD = Application.FileDialog(msoFileDialogFolderPicker)
    With FD
        .AllowMultiSelect = False
        If .Show = -1 Then
            sPath = .SelectedItems(1)
        Else 'cancel selected
            Exit Sub
        End If
    End With
        
'get files
    Set FSO = New FileSystemObject
    Set FIs = FSO.GetFolder(sPath).Files
    
    For Each FI In FIs
        'Test to ensure the file is what you want, then
        'do something
        Debug.Print FI.Name
    Next FI
    
End Sub

For "testing the file" to see if it is what you want, you could, for example

check the file name that it contains some known string (eg using Instr or Like)
check the file attributes to ensure, eg, it is not a system or hidden file:
Attribute 32 is the archive bit, so we strip that out
If FI.Attributes Mod 32 < 2 Then ...
Any other tests you can think of

How do I change my code to pick all the files within a folder instead of manually selecting them?

1 Answers1