Combine multiple csv files into one sheet VBA, non responsive, Locals FileName shows ""

[Mandy519]

Hi all,

I am trying to combine multiple CSV files into one worksheet via VBA. Yet, it seems that the filename cannot be recognized and no debug message was shown. Could somebody help? Thank you so much!!!

Option Explicit

Sub MergeAllWorkbooks()
    Dim SummarySheet As Worksheet
    Dim FolderPath As String
    Dim NRow As Long
    Dim FileName As String
    Dim WorkBk As Workbook
    Dim SourceRange As Range
    Dim DestRange As Range
    Dim LastRow As Long
    
    
    ' Create a new workbook
    Set SummarySheet = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
    
    ' Set folder path to point to the files.
    FolderPath = "C:\Users\wuman\Documents\TEST\GVO"
    
    NRow = 1
    
    FileName = Dir(FolderPath & "*.csv")
    
    ' Do while loop
    Do While FileName <> ""
        ' Open a workbook in the folder
        Set WorkBk = Workbooks.Open(FolderPath & FileName)
        
        SummarySheet.Range("A" & NRow).Value = FileName
        
        ' Set the source range
        LastRow = WorkBk.Worksheets(1).Cells.Find(what:="*", _
            after:=WorkBk.Worksheets(1).Cells.Range("A1"), _
            searchdirection:=xlPrevious, _
            LookIn:=xlFormulas, _
            searchorder:=xlByRows).Row
        Set SourceRange = WorkBk.Worksheets(1).Range("A3:A" & LastRow)
        
        ' Set the destination range to start at column B and
        ' be the same size as the source range.
        Set DestRange = SummarySheet.Range("B" & NRow)
        Set DestRange = DestRange.Resize(SourceRange.Rows.Count, _
           SourceRange.Columns.Count)
           
        ' Copy over the values from the source to the destination.
        DestRange.Value = SourceRange.Value
        
        ' Increase NRow
        NRow = NRow + DestRange.Rows.Count
        
        ' Close the source workbook without saving changes.
        WorkBk.Close savechanges:=False
        
        ' Use Dir to get the next file name.
        FileName = Dir()
    Loop

    
    ' Call AutoFit on the destination sheet so that all
    ' data is readable.
    SummarySheet.Columns.AutoFit
End Sub

 

[DeBeuz]

Hi,

Try appending "\" to the folderpath.

Paul