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.
Check out Kite (free AI Coding Assistant) → Link
Buy Me a Coffee? Your support is much appreciated!
PayPal Me: https://www.paypal.me/jiejenn/5
Venmo: @Jie-Jenn
Source Code:
Check out Kite (free AI Coding Assistant) → Kite URL
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
