Process all Files in a Folder

You might have a collection of files in a folder and want to take some action on each of them. They might not even all be spreadsheets, but you want to print or consolidate data from each of them. Here are a few procedures you might use to do so.

The following procedure uses a global Constant variable to loop through a folder and pass each filename to another procedure. The reason we use a global Constant will be shown below.

Const FOLDER As String = "C:\My Files\"

Sub ProcessEachFileInFolder()

On Error GoTo ErrorHandler

Dim fileName As String

  fileName = Dir(FOLDER, vbDirectory)

  ' loop through folder
  Do While Len(fileName) > 0
    Call ProcessFile(fileName)
    fileName = Dir ' pick up next filename, if available
  Loop

ProgramExit:
  Exit Sub
ErrorHandler:
  MsgBox Err.Number & " - " & Err.Description
  Resume ProgramExit
End Sub

The procedure called by the above code is called ProcessFile. That code can take any form you want, depending on what you want to do with each file found. Here's a stock procedure that opens the given workbook and sets an object reference to it. The global Constant is used to open the file, but we could easily have concatenated the path with the filename and passed it to the function instead.

Sub ProcessFile(fileName As String)

Dim currentWkbk As Excel.Workbook

  ' open workbook
  Set currentWkbk = Excel.Workbooks.Open(FOLDER & fileName)

' do whatever you need to do with currentWkbk

End Sub

This procedure assumes that you are processing a group of spreadsheets in a folder. If the folder contains a mixed group of files, you'll want to check the file type before calling the ProcessFile procedure in the first macro. For example:

Sub ProcessEachFileInFolder()

On Error GoTo ErrorHandler

Dim fileName As String

  fileName = Dir(FOLDER, vbDirectory)

  ' loop through folder, only process .xls files
  Do While Len(fileName) > 0
    If Right$(fileName, 3) = "xls" Then
      Call ProcessFile(fileName)
    End If
    fileName = Dir
  Loop

ProgramExit:
  Exit Sub
ErrorHandler:
  MsgBox Err.Number & " - " & Err.Description
  Resume ProgramExit
End Sub

This is the quick and dirty way of checking file extension, normally I would use a separate function for this:

Function GetFileType(fileName As String) As String
' get file extension
  GetFileType = Mid$(fileName, InStrRev(fileName, ".") + 1, Len(fileName))

End Function

And write another function that uses the GetFileType procedure to check if the file extension is an Excel type (.xla,.xls,.xlam,.xlsx, and so on).

Function IsExcelFileType(fileName As String)
' returns True is file extension starts
' with 'xl', i.e. xla, xlam, xlsx, etc
IsExcelFileType = (UCase$(Left$(GetFileType(fileName), 2)) = "XL")
End Function

Then the original function would look like this:

Sub ProcessEachFileInFolder()

On Error GoTo ErrorHandler

Dim fileName As String

  fileName = Dir(FOLDER, vbDirectory)

  ' loop through folder
  Do While Len(fileName) > 0
    If IsExcelFileType(fileName) Then
      Call ProcessFile(fileName)
    End If
    fileName = Dir
  Loop

ProgramExit:
  Exit Sub
ErrorHandler:
  MsgBox Err.Number & " - " & Err.Description
  Resume ProgramExit
End Sub