Loop through files in a folder using Dir

Do some process on the files in a folder

The code below shows how you can process the files in a folder. FileSpec is the filter you can adapt to only retrieve files whose name follow a certain pattern, e.g. *.xslx to get all Excel files with extension xslx.

If you look at the code you see that only the first time Dir has the FileSpec argument. This first call returns the first file. Subsequent calls, inside the loop, retrieve extra filenames, until an empty string is returned meaning there are no more files to process.

Note: An alternative way to do inspect the folder is using File System Object, which is flexible. Advantages of using Dir are 1. it is native in VBA so you don't have to reference the FSO library, 2. Dir is fast.

Dim strFileName As String
'TODO: Specify path and file spec
Dim strFolder As String: strFolder = "C:\temp\"
Dim strFileSpec As String: strFileSpec = strFolder & "*.*"
strFileName = Dir(strFileSpec)
Do While Len(strFileName) > 0
    'TODO: replace Debug.Print by the process you want to do on the file
    'Dim strFilePath As String: strFilePath = strFolder & strFileName
    Debug.Print strFileName
    strFileName = Dir
Loop

Create array of filenames for a given folder

In the code below the filenames are stored in an array. This allows you to separate the specific activity of processing from the activity of finding the files to process using Dir.

Dim strFileName As String
'TODO: Specify path and file spec
Dim strFolder As String: strFolder = "C:\temp\"
Dim strFileSpec As String: strFileSpec = strFolder & "*.*"
Dim FileList() As String
Dim intFoundFiles As Integer
strFileName = Dir(strFileSpec)
Do While Len(strFileName) > 0
    ReDim Preserve FileList(intFoundFiles)
    FileList(intFoundFiles) = strFileName
    intFoundFiles = intFoundFiles + 1
    strFileName = Dir
Loop

The image below shows the part of the Code VBA (download) menu that lets you insert the code fragment you require.