Shamima Sultana
It is very common to convert CSV files to Excel files. In this article, you will get how you can Merge Multiple CSV Files into one Excel Workbook VBA. Here, I explained two efficient codes so that you can do the task blink of an eye.
Download Practice Workbook
2 Examples of Using VBA to Merge Multiple CSV Files into One Workbook in Excel
1. Merging CSV Files into One New Workbook in Excel
Let me show you how you can use the VBA Editor to Merge Multiple CSV files into one Excel Workbook VBA.
- First, you need to keep all the CSV files in a particular Folder. Here, I kept it there.
If your Developer tab is not available in the Ribbon then check out this link.
- Then, from the Developer tab >> select Visual Basic.
Next, a window of Microsoft Visual Basic for Applications.
- From Insert >> select Module.
- Type the following code in the Module.
Option Explicit
Sub Merge_Multiple_CSV_Files()
Dim Dir_Path As String
Dim CSV_FileName As String
Dim Merged_Files As Workbook
Dim Merged_WB As Workbook
Dir_Path = "C:\Users\shami\OneDrive\Desktop\SoftEko\Merge Multiple CSV Files into One Workbook\"
If Dir_Path Like "*[!\/]" Then
Dir_Path = Dir_Path & "/"
End If
CSV_FileName = Dir(Dir_Path & "*.csv")
Set Merged_Files = Workbooks.Add
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Do While CSV_FileName <> vbNullString
Set Merged_WB = Workbooks.Open(Dir_Path & CSV_FileName)
Merged_WB.ActiveSheet.UsedRange.Copy Merged_Files.ActiveSheet _
.UsedRange.Rows(Merged_Files.ActiveSheet.UsedRange.Rows.Count).Offset(2).Resize(1)
Merged_WB.Close False
CSV_FileName = Dir()
Loop
Merged_Files.ActiveSheet.Rows(1).EntireRow.Delete
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
Code Breakdown
- Here, I created a Sub Procedure and named it Merge_Multiple_CSV_Files. Then declared some required variables.
- Then in the Dir_Path variable, I kept the folder path Address of a directory. It will be different in your case.
- If you forget the last sign, I used an IF function to check it and merge it with the Path Address.
- Next, by using the VBA Dir function you will get the file/directory name.
- Then, used Workbooks.Add to add a new workbook.
- Next, used a Do While loop to open the Workbooks by using the used path then the used range of those sheets will be copied to the new Excel workbook, I also used Offset to keep 2 rows empty after copying a CSV File.
- If there exists anything in the first row of the merged sheet then it will be deleted.
- Now, Save the code and click on F5 or Run Button.
Finally, you will get the new Workbook with all the CSV files available in that folder. All the CSV files are merged in one sheet.
The new workbook’s name is Book2.
If you want, you can Format the sheet based on your choice.
Here is the sheet containing some formats.
Read More: Merge CSV Files into Multiple Sheets in Excel (with Easy Steps)
2. Merging Multiple CSV Files into Existing Excel Workbook
It is also possible to Merge Multiple CSV files into an Existing workbook instead of creating a new one.
- To begin with, from the Developer tab >> select Visual Basic.
Next, a window of Microsoft Visual Basic for Applications.
- From Insert >> select Module.
- Insert the following code in the Module.
Option Explicit
Sub Merge_CSV_Files_Existing_WB()
Dim Merged_WB As Workbook
Dim Merged_Sheet As Worksheet
Dim Dir_Path As String, CSV_FileName As String
Dim Last_Row As Long
Set Merged_Sheet = ThisWorkbook.Worksheets("Merged Sheet")
Dir_Path = "C:\Users\shami\OneDrive\Desktop\SoftEko\Merge Multiple CSV Files into One Workbook\"
CSV_FileName = Dir(Dir_Path & "*.csv")
If CSV_FileName = vbNullString Then
MsgBox "No CSV Files", vbInformation
Else:
Merged_Sheet.Cells.ClearContents
End If
Do While CSV_FileName <> vbNullString
Set Merged_WB = Workbooks.Open(Dir_Path & CSV_FileName)
Last_Row = Merged_Sheet.Cells(Rows.Count, "A").End(xlUp).Row
Merged_WB.Worksheets(1).Range("A1").CurrentRegion. _
Copy Merged_Sheet.Cells(Last_Row + 1, "A").Offset(2)
Merged_WB.Close False
Set Merged_WB = Nothing
CSV_FileName = Dir()
Loop
Merged_Sheet.Rows(1).Delete
Merged_Sheet.Range("A1").CurrentRegion.RemoveDuplicates 1, xlNo
Set Merged_Sheet = Nothing
End Sub
Code Breakdown
- Here, I created a Sub Procedure and named it Merge_CSV_Files_Existing_WB. Then declared some required variables.
- As I will merge all the CSV files in the existing workbook, so I defined the sheet name which is Merged Sheet.
- Next, in the Dir_Path variable, I kept the folder path Address of a directory. It will be different in your case.
- Then, by using the VBA Dir function you will get the file/directory name.
- And I used an IF function to check if the Path Address contains any CSV files or not.
- Then, used a Do While loop to open the Workbooks by using the used path then it will find out the last row of the CSV file then will copy it to the new Excel sheet.I also used Offset to keep 2 rows empty after copying a CSV File.
- If there exists anything in the first row of the merged sheet, then it will be deleted also it will remove duplicates from the current region.
- Now, Save the code and go back to your Excel file.
- Then from the Developer tab >> select Macros.
A dialog box of Macro will appear.
- Now, select the Macro named Merge _CSV_Files_Existing_WB >> then click on Run.
Therefore, all the CSV files available in the particular folder are Merged in the existing workbook.
Now, you can Format the data. Here, I used some Fill Colors, Border, etc.
Read More: How to Import CSV into Existing Sheet in Excel (5 Methods)
Things to Remember
- Carefully insert the Directory/File path otherwise code won’t be able to merge any CSV files.
Conclusion
In this article, I tried to explain how to merge multiple CSV files into one Excel Workbook by using VBA. This explanation will help you to understand the ways. Lastly, if you have any kind of suggestions, ideas, or feedback please feel free to comment down below.
Related Articles
- CSV File Not Opening Correctly in Excel (4 Cases with Solutions)
- How to Open CSV with Delimiter in Excel (6 Simple Ways)
- Convert CSV to XLSX Command Line (with Easy Steps)
- Excel VBA: Read a Text File Line by Line (6 Related Examples)
- How to Import Text File to Excel Automatically (2 Suitable Ways)
- Convert CSV to Excel Automatically with Easy Steps
- Excel VBA to Import CSV File without Opening (3 Suitable Examples)
