Combine All Workbooks In Folder Excel VBA Macro

In this post we learn how to combine all the Excel files in a given folder using VBA macros. The macro triggers a dialog box to select a folder from the local drive, and then loops through the workbooks in that folder to copy the contents to the recipient workbook. That includes all the worksheets in each of the workbooks within the target folder. Use the code below to combine all Excel files in a folder into one single workbook.

Macro/VBA code:

 

  Sub CombineAllWorkbooksInFolder()

 

      Dim myDialog As FileDialog, myFolder As String, myFile As String

 

      Set myDialog = Application.FileDialog(msoFileDialogFolderPicker)

 

      If myDialog.Show = -1 Then

          myFolder = myDialog.SelectedItems(1) & Application.PathSeparator

          myFile = Dir(myFolder & "*.xls*")

          Do While myFile <> ""

              Workbooks.Open myFolder & myFile

              Workbooks(myFile).Worksheets.Copy After:=ThisWorkbook.ActiveSheet

               Workbooks(myFile).Close

               myFile = Dir

          Loop

      End If

 

  End Sub

 

 

Macro explained:

• First we declare a FileDialog object variable (myDialog) that represents the application file dialog. We also declare two string variables to store the folder and file names.
• We use the FileDialog property of the Application object to define a dialog object set to the variable myDialog. We use the folder picker dialog option with msoFileDialogFolderPicker. FileDialog accepts four possible constants:
• msoFileDialogFilePicker. Allows user to select a file
• msoFileDialogFolderPicker. Allows user to select a folder
• msoFileDialogOpen. Allows user to open a file
• msoFileDialogSaveAs. Allows user to save a file

• We check if a folder was picked with the Show property of the dialog object. If a folder was picked the result is -1, otherwise it would be 0.
• Then we use the SelectedItems property of the dialog object to get the name of the folder into the variable MyFolder. Next, we use the Dir function to get the name of all Excel files within that folder. The asterisk symbol (*) refers to all types of files. That can be changed to a specific name and/or extension.
• At this point, we loop through each file in the folder, whihch are stored in MyFile, using the Dir function at the end of the loop to jump to the following file each time. Within the Do loop, we perform the following tasks:
• First we open the workbook specifying the folder and file name (using MyFolder and MyFile variables)
• Then we copy all the worksheets in that workbook and paste them after the active sheet of the recipient workbook (the one with this macro)
• Finally, we close that workbook and move to the next file name using the Dir function.

• The Do loop repeats as many times as workbooks in the target folder. The VBA Dir function works in the same way the DOS command does to list all the files in a directory. It is a very useful function when dealing with files and directories in Excel. In this example, the Dir function represents the pillar of the macro, because it allows to work with all the Excel files in the selected directory or folder.

 

This is how we combine all the workbooks in a folder in Excel with VBA macros.

 

Other examples:

• How to Copy Columns To Separate Sheets with Excel VBA macros
  
• How to Stack Combine Multiple Columns with Excel VBA macros
  
• How to Strike Through Text In Cell with Excel VBA macros