I have a solution which copies the csv data to excel sheet but it works for only 1 CSV file to 1 Excel sheet. How to transfer multiple CSV files to multiple sheets in Excel?
Here is my code:
Dim oExcel
Set oExcel = CreateObject("Excel.Application")
With oExcel
.DefaultSaveFormat=51
.Workbooks.Open sourceFile
.Sheets(1).Columns("A").TextToColumns .Range("A1"), , , , , True
.Sheets.add
.ActiveWorkbook.SaveAs outputFile, 51
.Quit
End With
You actually don't need to use
TextToColumns
. For the first CSV file, the sheet is already there and for the second one, you can open the file as another workbook, then use theWorksheet.Copy
method to copy the sheet after the first one.There you go:
To make it more generic, you can adjust the previous code to work with any number of CSV files: