Excel VBA: Merge Multiple CSV Files into One Workbook

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.

Table of Contents hide

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

2. Merging Multiple CSV Files into Existing Excel Workbook

Things to Remember

Conclusion

Download Practice Workbook

Merging Multiple CSV Files into One Workbook.xlsm

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.

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.

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.