Combining csv sheets to one - code error

[Brutusar]

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?

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

 

[Alex Blakenburg]

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.

That seems to indicate that ther very first occurence of FileName = ""

If you add the line in blue in the position show ie after the initial retrieve.
What does it show in the immediate window ?
(Ctrl+G if you can't see it)
If it visually looks correct, then copy what is in the immediate window and in Excel go File > Open and paste it into the file box.
Does it show you that folder with the list of csv files ?
If not then you don't have any ".csv" extension files in that folder and you need to trouble shoot that first.

    ' Retrieve the first CSV file in the folder.
    FileName = Dir(FolderPath & "*.csv")
    Debug.Print FolderPath & "*.csv"