Looping through files in a Folder Excel VBA

I can't tell you how many times I have written an Excel VBA program that requires me to loop through various files in a folder!  In the past, I have used a file system object to do it, but I just recently discovered a new way to do it!  The solution is simple: use Dir!

As it turns out, Dir() allows you to use wildcards to access file names in a given folder. In order to get through each item in the folder, you simply run the Dir() command again with no arguments. It's easy, simple, fast, and effective. This is great when you want to search for a file within a folder in VBA, but you are not sure what the file will be called.

Use the following code to loop through files in a folder

 Sub LoopThroughFilesInFolder()  
      'Example Code BizzareExcelTips Blogspot  
      Dim sDirectory as String  
      Dim sFileName as String  
      sDirectory = "C:\*" 'Enter your folder path here with a wildcard (*) at the end  
      'get the filename (and make sure the file exists)  
      sFileName = Dir(sDirectory)  
      'The loop  
      While len(sFileName) > 0  
           'Your code  
           Debug.Print sFileName 'Do something with the file here  
           sFileName = Dir       'Get the next file  
      Wend  
 End Sub  

That's it! Nice and easy! Enjoy!