VBA Find all XLSM Files in a Folder using built-in Dir() function. In this tutorial we loop through a folder or directory and list all available .XLSM files in a folder. Lets learn how to use Dir, Right and Len VBA functions in this tutorial.
Let us see the example macro to loop through all .XLSM files in a folder using Excel VBA. The output is displayed in the immediate window. We are using Dir function to access all available files in a folder. Right function is used to check ‘\’ is added or not to file path and .XLSM is available in file name or not. And finally Len function is used to check file length.
'VBA Loop Through .xlsm files in a given Folder Sub VBAF1_List_All_XLSM_Files_Using_Dir() 'Variable Declaration Dim sFilePath As String Dim sFileName As String 'Specify File Path sFilePath = "C:\Test" 'Check for back slash If Right(sFilePath, 1) <> "\" Then sFilePath = sFilePath & "\" End If sFileName = Dir(sFilePath & "*.xlsm") Do While Len(sFileName) > 0 If Right(sFileName, 4) = "xlsm" Then 'Display file name in immediate window Debug.Print sFileName End If 'Set the fileName to the next available file sFileName = Dir Loop End Sub
Output: Here is the output screenshot of above macro.
You can refer the following link for the step by step instructions.
Click on the following links of the useful resources. These helps to learn and gain more knowledge.