Excel VBA: Merge Multiple CSV Files into One Workbook 

Get FREE Advanced Excel Exercises with Solutions!

It is very common to convert CSV files to Excel files. In this article, you will learn how you can Merge Multiple CSV Files into one Excel Workbook with VBA. Here, I explained two efficient codes so that you can do the task blink of an eye.


How to Merge Multiple CSV Files into One Excel Workbook using VBA: 2 Examples

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.

Multiple CSV Files into One Specific Folder

If your Developer tab is not available in the Ribbon then check out this link.

  • Then, from the Developer tab >> select Visual Basic.

 To Merge Multiple CSV Files into One Excel Workbook Opening VBA

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 to Merge Multiple CSV Files into One Excel Workbook

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.

click on Run to merge multiple csv files into one excel workbook

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.

 To Merge Multiple CSV Files into One Excel Workbook Opening VBA

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

VBA Code to merge multiple csv files into one excel workbook

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.

Run the Macro to Merge Multiple CSV Files into One Excel Workbook

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.

merged multiple csv files into one excel workbook

Read More: Excel VBA to Convert CSV File to XLSX


Things to Remember

  • Carefully insert the Directory/File path otherwise code won’t be able to merge any CSV files.

Download Practice Workbook


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shamima Sultana
Shamima Sultana

Shamima Sultana, BSc, Computer Science and Engineering, East West University, Bangladesh, has been working with the ExcelDemy project for 2 years. She has written over 75+ articles and reviewed 1000+ articles for ExcelDemy. She has also led several teams with Excel VBA and Content Development works. Currently, she is working as the Project Manager and oversees the day-to-day work, leads the services team, allocates resources to the right area, etc. Her work and learning interests vary from Microsoft... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo