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.