Excel macro to combine multiple CSV files (with Excel VBA)

In this Excel automation tutorial, I will show you how we can write a macro (using Excel VBA) to combine multiple CSV files.

Imaging your boss asks you to combine 10 or 50 CSV files by the end of the day, and you only have couple hours left. If you are going to do this task manually, it will takes a few hours. However, if you can automate this task, then everything can be done in let’s say 5 minutes.

Buy Me a Coffee? Your support is much appreciated!

PayPal Me: https://www.paypal.me/jiejenn/5
Venmo: @Jie-Jenn

Source Code:

Sub Merge_CSV_Files()
    
    Dim target_workbook As Workbook
    Dim data_sheet As Worksheet
    Dim folder_path As String, my_file As String
    Dim LastRow As Long
    
    Set data_sheet = ThisWorkbook.Worksheets("Master")
    
    folder_path = "C:\Users\Jie\Google Drive\(YouTube Folder)\_To Upload\Macro to combine CSV Files into One\"
    
    my_file = Dir(folder_path & "*.csv")
    
    '// Step 1: Clear worksheet
    If my_file = vbNullString Then
        MsgBox "CSV files not found.", vbInformation
    Else:
        data_sheet.Cells.ClearContents
    End If
    
    '// Step 2: Iterate CSV Files
    Do While my_file <> vbNullString
        Set target_workbook = Workbooks.Open(folder_path & my_file)
            
        LastRow = data_sheet.Cells(Rows.Count, "A").End(xlUp).Row
        
        target_workbook.Worksheets(1).Range("A1").CurrentRegion.Copy data_sheet.Cells(LastRow + 1, "A")
        target_workbook.Close False
        
        Set target_workbook = Nothing
        
        my_file = Dir()
    Loop

    '// Step 3: Clean up
    data_sheet.Rows(1).Delete
    data_sheet.Range("A1").CurrentRegion.RemoveDuplicates 1, xlNo
    
    Set data_sheet = Nothing

End Sub

2 Comments