VBA File Name Loop with Extension and Folder FileDialogFolderPicker

In this article, I show you how to pull all the file names from a particular folder into an excel workbook. It pulls the file names and the file extensions.

I'll just paste the code here and then explain it section by section afterwards. Here's the file and then the code.

File:

---

Code Module 1:

Sub FolderAndFileMacro()


Dim mypath As String
Dim myfile As String
Dim myfile2
Dim myextension As String
Dim wb As Workbook
Dim FldrPicker As FileDialog


Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

With FldrPicker
      .Title = "Select A Target Folder"
      .AllowMultiSelect = False
        If .Show <> -1 Then GoTo NextCode
        mypath = .SelectedItems(1) & "\"
End With

'In Case of Cancel
NextCode:
  mypath = mypath
  If mypath = "" Then GoTo ResetSettings


'pull file names from Module 2 called GrabFilenamesfromFolder. It pulls the file names from the folder you select and set to mypath variable.
GrabFilenamesfromFolder (mypath)


erow = Range("A" & Rows.Count).End(xlUp).Row
Range("B1").Select
ActiveCell.FormulaR1C1 = "=SEARCH(""re"",RC[-1],1)"
Range("B1").Select
Selection.AutoFill Destination:=Range("B1" & ":" & "B" & erow), Type:=xlFillDefault
Columns("B").Select
Selection.Copy
Columns("B").Select
Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks:=False, Transpose:=False
 Columns("B").Select

 Selection.Replace What:="#VALUE!", Replacement:="0", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
Dim counter As Double
counter = 1
Do Until counter > erow
If Range("B" & counter).Value <> 0 Then
myfile2 = Range("A" & counter).Value
End If
counter = counter + 1
Loop
        


myfile = Dir(mypath & myextension)

'Target File Extension (must include wildcard "*")
  myextension = "*.xls*"

'Loop through each Excel file in folder
Do While myfile <> ""
    'Set variable equal to opened workbook
      If myfile2 = myfile Then
      Set wb = Workbooks.Open(Filename:=mypath & myfile)
      End If
      myfile = Dir
Loop

ResetSettings:
If 1 = 1 Then
End If

End Sub

Code Module 2:

Sub GrabFilenamesfromFolder(path As String)
    Dim currentPath As String, directory As Variant
    Dim dirCollection As Collection
    Set dirCollection = New Collection
    Dim counter As Double
    counter = 1
    currentPath = Dir(path, vbDirectory)
    
    'Explore current directory
    Do Until currentPath = vbNullString
        Debug.Print currentPath
        'If Left(currentPath, 1) <> "." And _
            '(GetAttr(path & currentPath) And vbDirectory) = vbDirectory Then
            dirCollection.Add currentPath
        'End If
        currentPath = Dir()
        Range("A" & counter).Value = currentPath
        counter = counter + 1
    Loop
    
   
End Sub

---

Why is this useful: If you have a file that is the same file but the filename changes each month, then you can pull the data from the file just by selecting which folder the file is in without having to write anything. This method also avoids the likelihood that hardcoded file names won't pull the data properly because the file name changes so often.

---

Explanation of Code:

---

What is the File Dialog? The below is the file dialog. In the above code, mypath = .selecteditems(1) sets the mypath variable equal to whichever folder you select.

---

The next section of code basically runs the subprocedure in Module 2 from Module 1. If you don't know what modules are then you can watch my main VBA lessons on my Youtube playlist: https://www.youtube.com/playlist?list=PLsbzYG2Git5tsU0ky398r2KCa16bcxgOQ This next section of code basically reads in the file names and file extensions in mypath that you selected: it sets the Excel Workbook Sheet cells A1 until A100 (etc) until the last file in the folder equal to the file names and file extensions. In other words, the more files there are in the folder, the more file names there will be in column A.

---