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

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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
448
Alex Blakenburg
A
G
  • Solved
Dir(FolderPath) - multiple file formats
Replies
1
Views
335
DanteAmor
DanteAmor
H
  • Question
Macro to limit browsing to specific File
Replies
2
Views
233
howard
H
C
  • Solved
Macro works for one file, does not work for all files in the directory
2
Replies
13
Views
493
Alex Blakenburg
A
H
  • Solved
Macro to convert Files into CSV Files-limit selection when browsing
Replies
1
Views
249
howard
H

Forum statistics

Threads
1,215,077
Messages
6,122,991
Members
449,094
Latest member
masterms

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