Rifat Hassan
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
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.
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: [Fixed!] Unable to Open Multiple Excel Files (8 Reasons with Solutions)
Similar Readings
- How to Open Multiple Excel Files in One Window (4 Easy Methods)
- Unable to Open Two Excel Files at the Same Time (13 Probable Solutions)
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: [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.
