Combining csv sheets to one - code error

B

Brutusar

Board Regular
Joined
Nov 23, 2019
Messages
166
Office Version
  1. 365
Platform
  1. Windows
Hi,

I have written a code that is supposed to combine all CSV sheets in a folder to one sheet. The code is running without errors, apart from no data is being copied to the new file. Can somebody find my mistake?


VBA Code: 
Sub CombineCSVSheets()

    Dim FolderPath As String
    Dim FileName As String
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim wsCombined As Worksheet
    Dim LastRow As Long
    Dim CurrentRow As Long
    
    ' Define the folder path containing the CSV files
    FolderPath = "C:\Products\AllProducts"
    
    ' Create a new workbook to combine the CSV sheets
    Set wb = Workbooks.Add
    
    ' Retrieve the first CSV file in the folder
    FileName = Dir(FolderPath & "*.csv")
    
    ' Loop through all CSV files in the folder
    Do While FileName <> ""
        ' Open the CSV file
        Workbooks.Open FileName:=FolderPath & FileName
        
        ' Copy each row of data from the CSV sheet to the combined sheet
        Set ws = ActiveSheet
        Set wsCombined = wb.Worksheets.Add(After:=wb.Worksheets(wb.Worksheets.Count))
        ws.Rows(1).Copy wsCombined.Rows(1)
        LastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
        CurrentRow = wsCombined.Cells(wsCombined.Rows.Count, 1).End(xlUp).Row + 1
        ws.Range("A1", "A" & LastRow).Copy wsCombined.Cells(CurrentRow, 1)
        
        ' Close the CSV file without saving changes
        wb.Worksheets(wsCombined.Name).Delete
        wb.Close SaveChanges:=False
        
        ' Retrieve the next CSV file in the folder
        FileName = Dir
    Loop
    
    ' Save and close the combined workbook
    wb.SaveAs "C:\products\AllProducts\Combined"
    wb.Close SaveChanges:=True
End Sub
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
Brutusar said:
Hi,

I have written a code that is supposed to combine all CSV sheets in a folder to one sheet. The code is running without errors, apart from no data is being copied to the new file. Can somebody find my mistake?


VBA Code: 
Sub CombineCSVSheets()

    Dim FolderPath As String
    Dim FileName As String
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim wsCombined As Worksheet
    Dim LastRow As Long
    Dim CurrentRow As Long
   
    ' Define the folder path containing the CSV files
    FolderPath = "C:\Products\AllProducts"
   
    ' Create a new workbook to combine the CSV sheets
    Set wb = Workbooks.Add
   
    ' Retrieve the first CSV file in the folder
    FileName = Dir(FolderPath & "*.csv")
   
    ' Loop through all CSV files in the folder
    Do While FileName <> ""
        ' Open the CSV file
        Workbooks.Open FileName:=FolderPath & FileName
       
        ' Copy each row of data from the CSV sheet to the combined sheet
        Set ws = ActiveSheet
        Set wsCombined = wb.Worksheets.Add(After:=wb.Worksheets(wb.Worksheets.Count))
        ws.Rows(1).Copy wsCombined.Rows(1)
        LastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
        CurrentRow = wsCombined.Cells(wsCombined.Rows.Count, 1).End(xlUp).Row + 1
        ws.Range("A1", "A" & LastRow).Copy wsCombined.Cells(CurrentRow, 1)
       
        ' Close the CSV file without saving changes
        wb.Worksheets(wsCombined.Name).Delete
        wb.Close SaveChanges:=False
       
        ' Retrieve the next CSV file in the folder
        FileName = Dir
    Loop
   
    ' Save and close the combined workbook
    wb.SaveAs "C:\products\AllProducts\Combined"
    wb.Close SaveChanges:=True
End Sub
Click to expand...

A few issues.

Go through this to see how it should have been written.

VBA Code: 
Sub CombineCSVSheets()

    Dim FolderPath As String
    Dim FileName As String
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim wbCombined As Workbook
    Dim wsCombined As Worksheet
    Dim CurrentRow As Long
    
    ' Define the folder path containing the CSV files
    FolderPath = "C:\Products\AllProducts\"
    
    ' Create a new workbook to combine the CSV sheets
    Set wbCombined = Workbooks.Add
    With wbCombined
        Set wsCombined = .Worksheets.Add(After:=.Worksheets(.Worksheets.Count))
        .SaveAs FolderPath & "Combined.xlsx"
    End With
    
    wsCombined.Name = "Combined"
    
    ' Retrieve the first CSV file in the folder
    FileName = Dir(FolderPath & "*.csv")
        
    ' Loop through all CSV files in the folder
    Do While FileName <> ""
    
        ' Open the CSV file.
        Workbooks.Open FileName:=FolderPath & FileName
        
        Set ws = ActiveSheet
        
        If Len(Trim(wsCombined.Cells(1))) = 0 Then
            ws.Rows(1).Copy wsCombined.Rows(1)
        End If
                
        CurrentRow = wsCombined.Cells(wsCombined.Rows.Count, 1).End(xlUp).Row + 1
        
        ws.Range("A1").CurrentRegion.Offset(1, 0).Copy wsCombined.Cells(CurrentRow, 1)
                
        ActiveWorkbook.Close SaveChanges:=False
        
        ' Retrieve the next CSV file in the folder.
        FileName = Dir
        
    Loop
    
    ' Save and close the combined workbook.
    wbCombined.Close SaveChanges:=True
    
    MsgBox "Finished."

End Sub
 
Upvote 0
Herakles said:
A few issues.

Go through this to see how it should have been written.

VBA Code: 
Sub CombineCSVSheets()

    Dim FolderPath As String
    Dim FileName As String
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim wbCombined As Workbook
    Dim wsCombined As Worksheet
    Dim CurrentRow As Long
   
    ' Define the folder path containing the CSV files
    FolderPath = "C:\Products\AllProducts\"
   
    ' Create a new workbook to combine the CSV sheets
    Set wbCombined = Workbooks.Add
    With wbCombined
        Set wsCombined = .Worksheets.Add(After:=.Worksheets(.Worksheets.Count))
        .SaveAs FolderPath & "Combined.xlsx"
    End With
   
    wsCombined.Name = "Combined"
   
    ' Retrieve the first CSV file in the folder
    FileName = Dir(FolderPath & "*.csv")
       
    ' Loop through all CSV files in the folder
    Do While FileName <> ""
   
        ' Open the CSV file.
        Workbooks.Open FileName:=FolderPath & FileName
       
        Set ws = ActiveSheet
       
        If Len(Trim(wsCombined.Cells(1))) = 0 Then
            ws.Rows(1).Copy wsCombined.Rows(1)
        End If
               
        CurrentRow = wsCombined.Cells(wsCombined.Rows.Count, 1).End(xlUp).Row + 1
       
        ws.Range("A1").CurrentRegion.Offset(1, 0).Copy wsCombined.Cells(CurrentRow, 1)
               
        ActiveWorkbook.Close SaveChanges:=False
       
        ' Retrieve the next CSV file in the folder.
        FileName = Dir
       
    Loop
   
    ' Save and close the combined workbook.
    wbCombined.Close SaveChanges:=True
   
    MsgBox "Finished."

End Sub
Click to expand...
Hi, thanks for the code corrections. However, it does have the same problem. It is running without errors, but nothing is being copied. The new workbook is blank.
 
Upvote 0
Brutusar said:
Hi, thanks for the code corrections. However, it does have the same problem. It is running without errors, but nothing is being copied. The new workbook is blank.
Click to expand...
Are the CSV files in the C:\Products\AllProducts\ folder?

Do the CSV files have data in the first worksheet?

Does the Combined.xlsx file get created?
 
Upvote 0
Herakles said:
Do the CSV files have data in the first worksheet?
Click to expand...
CSV files are flat text files, and so by definition can only have one worksheet (cannot have a multi-page/sheet CSV file).
So I think a more accurate question to ask is if the CSV files have any data at all, and if so, is it in the first column (column A)?
If the rows of data start with a comma, then the first comma will be blank and the data will appear in the other columns.
 
Upvote 0
Herakles said:
Are the CSV files in the C:\Products\AllProducts\ folder?

Do the CSV files have data in the first worksheet?

Does the Combined.xlsx file get created?
Click to expand...
All CSV files are in the folder ( a total of 34)

all CSV files have data in column in the range A:E.

The Combined.xlsx file is being created, but outside the AllProducts folder (one level up, in the Products folder), and it is blank.
 
Upvote 0
Joe4 said:
CSV files are flat text files, and so by definition can only have one worksheet (cannot have a multi-page/sheet CSV file).
So I think a more accurate question to ask is if the CSV files have any data at all, and if so, is it in the first column (column A)?
If the rows of data start with a comma, then the first comma will be blank and the data will appear in the other columns.
Click to expand...
A different approach.

I created some text files using NOTEPAD with a .CSV extension.

VBA Code: 
Sub CombineCSVSheets()

    Dim FolderPath As String
    Dim FileName As String
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim wbCombined As Workbook
    Dim wsCombined As Worksheet
    Dim CurrentRow As Long
    Dim rngPaste As Range
    
    ActiveWorkbook.Save
    
    ' Define the folder path containing the CSV files
    FolderPath = "C:\Products\AllProducts\"
    
    ' Create a new workbook to combine the CSV sheets
    On Error Resume Next
    Kill (FolderPath & "Combined.xlsx")
    On Error GoTo 0
    Set wbCombined = Workbooks.Add
    With wbCombined
        Set wsCombined = .Worksheets.Add(After:=.Worksheets(.Worksheets.Count))
        .SaveAs FolderPath & "Combined.xlsx"
    End With
    
    wsCombined.Name = "Combined"
    
    ' Retrieve the first CSV file in the folder.
    FileName = Dir(FolderPath & "*.csv")
        
    ' Loop through all CSV files in the folder
    Do While FileName <> ""
    
        With wsCombined
            If Len(Trim(.Range("B1"))) = 0 Then
                Set rngPaste = .Range("A1")
            Else
                Set rngPaste = wsCombined.Range("B" & wsCombined.Cells(wsCombined.Rows.Count, 2).End(xlUp).Row + 1).Offset(0, -1)
            End If
        End With
            
        With wsCombined.QueryTables.Add(Connection:="TEXT;" & FolderPath & FileName, Destination:=rngPaste)
             .TextFileParseType = xlDelimited
             .TextFileCommaDelimiter = True
             .Refresh
        End With
               
        ' Retrieve the next CSV file in the folder.
        FileName = Dir
        
    Loop
    
    ' Save and close the combined workbook.
    wbCombined.Close SaveChanges:=True
    
    MsgBox "Finished."

End Sub
 
Upvote 0
Herakles said:
A different approach.

I created some text files using NOTEPAD with a .CSV extension.

VBA Code: 
Sub CombineCSVSheets()

    Dim FolderPath As String
    Dim FileName As String
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim wbCombined As Workbook
    Dim wsCombined As Worksheet
    Dim CurrentRow As Long
    Dim rngPaste As Range
   
    ActiveWorkbook.Save
   
    ' Define the folder path containing the CSV files
    FolderPath = "C:\Products\AllProducts\"
   
    ' Create a new workbook to combine the CSV sheets
    On Error Resume Next
    Kill (FolderPath & "Combined.xlsx")
    On Error GoTo 0
    Set wbCombined = Workbooks.Add
    With wbCombined
        Set wsCombined = .Worksheets.Add(After:=.Worksheets(.Worksheets.Count))
        .SaveAs FolderPath & "Combined.xlsx"
    End With
   
    wsCombined.Name = "Combined"
   
    ' Retrieve the first CSV file in the folder.
    FileName = Dir(FolderPath & "*.csv")
       
    ' Loop through all CSV files in the folder
    Do While FileName <> ""
   
        With wsCombined
            If Len(Trim(.Range("B1"))) = 0 Then
                Set rngPaste = .Range("A1")
            Else
                Set rngPaste = wsCombined.Range("B" & wsCombined.Cells(wsCombined.Rows.Count, 2).End(xlUp).Row + 1).Offset(0, -1)
            End If
        End With
           
        With wsCombined.QueryTables.Add(Connection:="TEXT;" & FolderPath & FileName, Destination:=rngPaste)
             .TextFileParseType = xlDelimited
             .TextFileCommaDelimiter = True
             .Refresh
        End With
              
        ' Retrieve the next CSV file in the folder.
        FileName = Dir
       
    Loop
   
    ' Save and close the combined workbook.
    wbCombined.Close SaveChanges:=True
   
    MsgBox "Finished."

End Sub
Click to expand...
Thanks, for some strange reason nothing is being copied. The Combined file is being generated, but without any values.
 
Upvote 0
Brutusar said:
Thanks, for some strange reason nothing is being copied. The Combined file is being generated, but without any values.
Click to expand...
A bit baffling.

Put the second line of code here below the first one to display the filename as it loops.

Do While FileName <> ""

MsgBox FileName
 
Upvote 0
When stepping thru the code, what happens is that it is just jumping over every line of code between "Do While FileName <> " and "wbCombined.Close SaveChanges:=True". Nothing in between these two lines are being run.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
combining csv workbooks to single sheet - vba works but need help adjusting
Replies
4
Views
641
Alex Blakenburg
A
G
  • Solved
Dir(FolderPath) - multiple file formats
Replies
1
Views
592
DanteAmor
DanteAmor
T
  • Solved
Open all files in a folder and run macro
Replies
3
Views
644
Trebor76
T
H
  • Question Question
Macro to Open File and limit selection where files contains "Parts" in C
Replies
4
Views
243
howard
H
B
  • Question Question
Excel VBA Searching Unique Variables to Match against Exported Schedule Files to Determine Date
Replies
1
Views
158
sijpie
sijpie

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top