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

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.

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.

 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: 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

Shamima Sultana
Shamima Sultana

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I'm working as a Project Manager at ExcelDemy. I am doing research on Microsoft Excel and here we will be posting articles related to this. My last educational degree was BSc and my program was in Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. I love to learn new things. Data Analysis is one of my favorite fields as I love to extract patterns based on problems. Here, I'm trying to provide enriched quality content regarding Excel basics to Advanced. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo