Merge multiple csv files in one excel sheet
32,473
After Set basebook = ThisWorkbook
Enter this:
Dim nextRow As Integer
Dim wsTotal As Worksheet
Set wsTotal = basebook.Worksheets("Total")
And here is the corrected For loop:
'Loop through all files in the array(myFiles)
If Fnum > 0 Then
For Fnum = LBound(MyFiles) To UBound(MyFiles)
'open file
Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum))
With wsTotal
'activate if you want (optional)
'.Activate
'copy all the data on the sheet
mybook.Worksheets(1).UsedRange.Copy
'find the next empty row
nextRow = .Range("A1").SpecialCells(xlCellTypeLastCell).Row + 1
'select if desired (optional)
'.Cells(NextRow, 1).Select
'paste the data
.Cells(nextRow, 1).PasteSpecial (xlPasteAll)
'turn off copy mode
Application.CutCopyMode = False
'Do you really want to change the worksheet name?
.Name = mybook.Name
End With
'close file
mybook.Close savechanges:=False
Next Fnum
Author by
Peter O
Updated on July 09, 2022Comments
-
Peter O almost 2 years
After searching a lot on the internet i tried to combine a working Excel VBA code that reads all .csv files in a folder into an excel file (each on a seperate worksheet). But the only thing i need is to combine all the csv files in 1 worksheet....
The working code is:
working file into seperate worksheets
Sub Example12() Dim MyPath As String Dim FilesInPath As String Dim MyFiles() As String Dim SourceRcount As Long Dim Fnum As Long Dim mybook As Workbook Dim basebook As Workbook 'Fill in the path\folder where the files are 'on your machine MyPath = "c:\Data" 'Add a slash at the end if the user forget it If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\" End If 'If there are no Excel files in the folder exit the sub FilesInPath = Dir(MyPath & "*.csv") If FilesInPath = "" Then MsgBox "No files found" Exit Sub End If On Error GoTo CleanUp Application.ScreenUpdating = False Set basebook = ThisWorkbook 'Fill the array(myFiles)with the list of Excel files in the folder Fnum = 0 Do While FilesInPath <> "" Fnum = Fnum + 1 ReDim Preserve MyFiles(1 To Fnum) MyFiles(Fnum) = FilesInPath FilesInPath = Dir() Loop 'Loop through all files in the array(myFiles) If Fnum > 0 Then For Fnum = LBound(MyFiles) To UBound(MyFiles) Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum)) mybook.Worksheets(1).Copy after:= _ basebook.Sheets(basebook.Sheets.Count) On Error Resume Next ActiveSheet.Name = mybook.Name On Error GoTo 0 ' You can use this if you want to copy only the values ' With ActiveSheet.UsedRange ' .Value = .Value ' End With mybook.Close savechanges:=False Next Fnum End If CleanUp: Application.ScreenUpdating = True End Sub --------------------------------------------------------- But the change i've made was to change the part where the VBA copies it into a sheet "after" the last one, to append it to a existing sheet "Totaal".
not working code --------------------------------------------------------- Sub Example12() Dim MyPath As String Dim FilesInPath As String Dim MyFiles() As String Dim SourceRcount As Long Dim Fnum As Long Dim mybook As Workbook Dim basebook As Workbook 'Fill in the path\folder where the files are 'on your machine MyPath = "c:\Data" 'Add a slash at the end if the user forget it If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\" End If 'If there are no Excel files in the folder exit the sub FilesInPath = Dir(MyPath & "*.csv") If FilesInPath = "" Then MsgBox "No files found" Exit Sub End If On Error GoTo CleanUp Application.ScreenUpdating = False Set basebook = ThisWorkbook 'Fill the array(myFiles)with the list of Excel files in the folder Fnum = 0 Do While FilesInPath <> "" Fnum = Fnum + 1 ReDim Preserve MyFiles(1 To Fnum) MyFiles(Fnum) = FilesInPath FilesInPath = Dir() Loop 'Loop through all files in the array(myFiles) If Fnum > 0 Then For Fnum = LBound(MyFiles) To UBound(MyFiles) Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum)) mybook.Worksheets(1).Copy **basebook.Sheets("Totaal").Select NextRow = Cells(Rows.Count, 0).End(xlUp).Row Cells(NextRow, 1).Select ActiveSheet.Paste** On Error Resume Next ActiveSheet.Name = mybook.Name On Error GoTo 0 ' You can use this if you want to copy only the values ' With ActiveSheet.UsedRange.Value = .Value ' End With mybook.Close savechanges:=False Next Fnum End If CleanUp: Application.ScreenUpdating = True End Sub
I haven't got the knowledge to change this :(. Was I on the right track?
All input will be greatly appreciated!
EXTRA INFO: The data in the CSV files are put in the first column. After the whole merging process i planned to do the split into columns afterwards....
Thanks!
-
Peter O almost 11 yearsIt doesn't work after these changes. It only opens 1 sheet and removes the Total sheet.
-
Peter O almost 11 yearsI am going to look into this.. It looks like a better solution, but i don't know how to implement this in my code at the moment.
-
D_Bester almost 11 yearsUse the debugger; press F8 to step through the code and see what is not working. Use Debug.Print to see what the variables are. I understood you only wanted one sheet. Also it doesn't remove the Total sheet; it just renames it. If you don't want that then remove that line.
-
Peter O almost 11 yearsThanks for the reply. I will check this tomorrow. Indeed i want to merge all csv files into 1 sheet. But in this case he imported 1 csv, renamed the sheet from Total to the name of the csv file and then stopped. I will dive into it tomorrow when i have more time for it! Thanks again for responding!
-
Peter O almost 11 yearsI commented out this line of code. Then the whole process went very well !! 'Do you really want to change the worksheet name? .Name = mybook.Name