Help looping through files in a directory

[statfreak]

I have the code below. It runs, but only counts the first file and then ends. There are 12 files in the directory that should have been written to the Files Processed worksheet and counted.

My understanding is the 'fil = Dir' line should advance to the next file, but it is just returning an empty string ("") instead of the next file.

Any suggestions and help appreciated.

Option Explicit

Sub countfiles()

    Dim z As Long, totfiles As Long
    Dim WS As Worksheet
    Dim fLdr As String, Fil As String, myCurrFile As String, workshtName As String
    
    'define variables
    myCurrFile = ThisWorkbook.name
    z = 0
    totfiles = 0
    
    Workbooks(myCurrFile).Activate
    
    'turn off screen updating while macro is running
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .EnableEvents = False
    End With
        
    Set WS = Sheets("Files Processed")

    fLdr = ActiveWorkbook.Path
    
    If Right(fLdr, 1) <> "\" Then fLdr = fLdr & "\"
    
    ChDir fLdr
    Fil = Dir("*.csv")
    
    Do While Fil <> ""
        z = z + 1
        WS.Cells(z + 1, 1).Resize(, 4) = _
            Array(Dir(Fil), _
            FileLen(Fil) / 1000, _
            FileDateTime(Fil), _
            fLdr)
        totfiles = totfiles + 1
        Fil = Dir
    Loop
    
    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
        .EnableEvents = True
        .StatusBar = False
    End With
    
End Sub

 

[VoG]

Possibly

Fil = Dir(fLdr & "*.csv")

 

[statfreak]

I tried that and it gets caught in an endless loop just repeating the first file over and over again.

Appreciate the reply though.

 

[VoG]

Sorry, I wasn't clear where I meant

 Fil = Dir(fLdr & "*.csv")
    
    Do While Fil <> ""
        Z = Z + 1
        WS.Cells(Z + 1, 1).Resize(, 4) = _
            Array(Dir(Fil), _
            FileLen(Fil) / 1000, _
            FileDateTime(Fil), _
            fLdr)
        totfiles = totfiles + 1
        Fil = Dir
    Loop

 

[statfreak]

Ah, I see. Tried using

Fil = Dir(fLdr & "*.csv")

instead of

Fil = Dir("*.csv")

and received the same result, only the first file is counted & written.

 

[VoG]

Sorry, I can't see why it is doing that. This is a similar piece of code that definitely works

Sub ListFiles()
Dim MyFolder As String
Dim MyFile As String
Dim j As Integer
MyFolder = "C:\example"
MyFile = Dir(MyFolder & "\*.xls")
Do While MyFile <> ""
    j = j + 1
    Cells(j, 1).Value = MyFile
    MyFile = Dir
Loop
End Sub

 

[statfreak]

Sorry, I can't see why it is doing that. This is a similar piece of code that definitely works

Sub ListFiles()
Dim MyFolder As String
Dim MyFile As String
Dim j As Integer
MyFolder = "C:\example"
MyFile = Dir(MyFolder & "\*.xls")
Do While MyFile <> ""
    j = j + 1
    Cells(j, 1).Value = MyFile
    MyFile = Dir
Loop
End Sub

Yes, it does.

Thank you for the help.