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

How 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.

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

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

⧪ 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

⧪ 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

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

Read More: How to Open Another Workbook and Copy Data with Excel VBA

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).

⧪ Step 3: Putting the VBA Code

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

⧪ 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.

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: Excel VBA to Loop Through Files in Folder and Copy Data

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.

Download Practice Workbook

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

Conclusion

Therefore, this is the process of opening all the Excel files in a folder and copying data from them using VBA. Do you have any questions? Feel free to ask us.

Related Articles

• How to Use Excel VBA to Copy Range to Another Excel Sheet
• Macro to Copy and Paste from One Worksheet to Another
• Macro to Copy Specific Columns from One Worksheet to Another in Excel
• Excel VBA to Copy Rows to Another Worksheet Based on Criteria
• Excel VBA: Copy Range to Another Workbook
• Excel VBA to Copy Data from Another Workbook without Opening
• Macro to Copy Data from One Workbook to Another Based on Criteria