How to Open All Excel Files in a Folder and Copy Data Using VBA

In this article, I’ll show you how you can open all the Excel files from a folder and copy data from them using VBA.


Open All Excel Files in a Folder and Copy Data Using VBA (Quick View)

Sub Open_All_Excel_Files_in_a_Folder_and_Copy_Data()

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

Sheet_Name = "January"
Set New_Workbook = ThisWorkbook

Set File_Dialog = Application.FileDialog(msoFileDialogFolderPicker)
File_Dialog.AllowMultiSelect = False
File_Dialog.Title = "Select the Excel Files"
If File_Dialog.Show <> -1 Then
    Exit Sub
End If

File_Path = File_Dialog.SelectedItems(1) & "\"
File_Name = Dir(File_Path & "*.xls*")

ActiveColumn = 0
Do While File_Name <> ""
    Set File = Workbooks.Open(Filename:=File_Path & File_Name)
    File.Worksheets(Sheet_Name).UsedRange.Copy
    ActiveColumn = ActiveColumn + 1
    New_Workbook.Worksheets(Sheet_Name).Cells(1, ActiveColumn).PasteSpecial Paste:=xlPasteAll
    ActiveColumn = ActiveColumn + File.Worksheets(1).UsedRange.Columns.Count
    File_Name = Dir()
Loop

End Sub

VBA Code to Open All Excel Files in a Folder and Copy Data Using VBA


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


An Overview of the VBA Code to Open All Excel Files in a Folder and Copy Data from Them (Step-by-Step Analysis)

So, without further delay, let’s go to our main discussion today. Here we’ve got a folder called ExcelDemy where there are 4 Excel Files named 2017, 2018, 2019, and 2020.

Excel Files to Copy All Excel Files in a Folder and Copy Data Using VBA

Each file contains three worksheets called January, February, and March. Each of them contains the sales record for these months.

Data Set to Copy All Excel Files in a Folder and Copy Data Using VBA

Our objective today is to open all these files and copy data from a specific worksheet (Say January) of all these files in a worksheet.

Let’s see the step-by-step procedure to achieve this.

⧪ Step 1: Setting Up the Environment

First of all, we’ll set some necessary parameters to the required values to set up an environment for running the code smoothly. These are:

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

⧪ Step 2: Inserting the Necessary Inputs

Next, we need to insert the necessary inputs to the code. These are:

  • The Worksheet Name to Copy Data from (“January” in this example)
  • The Destination Worksheet (ThisWorkbook in this example. If you want to copy to another workbook, use the name of that workbook along with the full path directory).
Sheet_Name = "January"
Set New_Workbook = ThisWorkbook

Inserting Inputs to Copy All Excel Files in a Folder and Copy Data Using VBA

⧪ Step 3: Selecting the Folder with the Excel Files

This is one of the most important steps. We’ll select the necessary folder (ExcelDemy) that contains our Excel files. We’ll use a VBA FileDialog object for this purpose.

Set File_Dialog = Application.FileDialog(msoFileDialogFolderPicker)
File_Dialog.AllowMultiSelect = False
File_Dialog.Title = "Select the Excel Files"
If File_Dialog.Show <> -1 Then
    Exit Sub
End If

Selecting the Folder to Copy All Excel Files in a Folder and Copy Data Using VBA

⧪ Step 4: Setting the Names of the Selected Folder and the Excel Files in It

Next, we’ll set the name of the selected folder and the Excel files in it. We’ll use the VBA Dir function for this purpose.

File_Path = File_Dialog.SelectedItems(1) & "\"
File_Name = Dir(File_Path & "*.xls*")

⧪ Step 5: Iterating Through a Loop to Open All the Files and Copy Data from Them

Finally, we’ll iterate through a while-loop to open all the files and copy data from them.

ActiveColumn = 0
Do While File_Name <> ""
    Set File = Workbooks.Open(Filename:=File_Path & File_Name)
    File.Worksheets(Sheet_Name).UsedRange.Copy
    ActiveColumn = ActiveColumn + 1
    New_Workbook.Worksheets(Sheet_Name).Cells(1, ActiveColumn).PasteSpecial Paste:=xlPasteAll
    ActiveColumn = ActiveColumn + File.Worksheets(1).UsedRange.Columns.Count
    File_Name = Dir()
Loop

Opening the Files to Copy All Excel Files in a Folder and Copy Data Using VBA

Therefore, the complete VBA code will be:

VBA Code:

Sub Open_All_Excel_Files_in_a_Folder_and_Copy_Data()

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

Sheet_Name = "January"
Set New_Workbook = ThisWorkbook

Set File_Dialog = Application.FileDialog(msoFileDialogFolderPicker)
File_Dialog.AllowMultiSelect = False
File_Dialog.Title = "Select the Excel Files"
If File_Dialog.Show <> -1 Then
    Exit Sub
End If

File_Path = File_Dialog.SelectedItems(1) & "\"
File_Name = Dir(File_Path & "*.xls*")

ActiveColumn = 0
Do While File_Name <> ""
    Set File = Workbooks.Open(Filename:=File_Path & File_Name)
    File.Worksheets(Sheet_Name).UsedRange.Copy
    ActiveColumn = ActiveColumn + 1
    New_Workbook.Worksheets(Sheet_Name).Cells(1, ActiveColumn).PasteSpecial Paste:=xlPasteAll
    ActiveColumn = ActiveColumn + File.Worksheets(1).UsedRange.Columns.Count
    File_Name = Dir()
Loop

End Sub

VBA Code to Open All Excel Files in a Folder and Copy Data Using VBA

Read More: [Fixed!] Unable to Open Multiple Excel Files (8 Reasons with Solutions)


Similar Readings


Developing a Macro to Copy All Excel Files in a Folder and Copy Data Using VBA

We’ve seen the step-by-step analysis of the code to open all Excel files in a folder and copy data from them.

Now we’ll see the step-by-step procedure to develop a Macro using the code.

⧪ Step 1: Opening the Visual Basic Window

Press ALT + F8 on your keyboard to open the Visual Basic window.

⧪ Step 2: Inserting a New Module

Go to the Insert > Module tool in the toolbar to insert a new module (Module1).

Inserting a Module to Copy All Excel Files in a Folder and Copy Data Using VBA

⧪ Step 3: Putting the VBA Code

Next, copy and paste the given VBA code into the new module.

Putting the VBA Code to Copy All Excel Files in a Folder and Copy Data Using VBA

⧪ Step 4: Running the Code

Run the code from the Run Sub / UserForm option in the toolbar.

⧪ Step 5: The Output

The moment you run the code, a File Dialog will open asking you to select the folder with your Excel files. Select the folder (Here, I’ve selected ExcelDemy) and click Open.

Selecting the Folder to Copy All Excel Files in a Folder and Copy Data Using VBA

You’ll find all the Excel files in the folder (2017, 2018, 2019, and 2020) opened and data copied from the input sheet (“January” here) of the files to the destination workbook (Active workbook here).

Read More: [Fixed!] Excel Files Not Opening from File Explorer (7 Quick Solutions)


Things to Remember

  • The code can copy not only all the .xlsx files but also all the files that have .xls in their extension (Like .xls, .xlsm, .xlsb,)
  • Keep the destination workbook in a different folder from the folder with the Excel files. Otherwise, you’ll face issues while running the code.
  • There must be a worksheet of the same name as the input worksheet (“January”) in the destination workbook before running the code. Otherwise, you’ll face errors.

Conclusion

Therefore, this is the process to open all the Excel files in a folder and copy data from them using VBA. Do you have any questions? Feel free to ask us. And don’t forget to visit our site ExcelDemy for more posts and updates.


Related Articles

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo