Looping through and opening a directory of files with Excel VBA Dir() function quitting early

Question

I need to loop through a directory of .csv files, retrieve a piece of information from each one and compile in a new .xlsx file.

The Dir() function is bailing well before looping through 500 files.

---

My solution based on this thread: VBA code to loop through files in a folder.

Apparently, while this function was executing, another thing (I assume a Word document I was writing) was telling the macro to stop with no error code. It was exiting out of a while -> wend loop leaving the last document the function visited opened and not executing the close statement. I confirmed this running the macro without anything else open.

If the Dir() function stops execution before checking every file in a directory, close other MicroSoft applications.

---

What exactly is going on? Is there a way to further mitigate the problem?

Answer 1

To avoid the Dir bug, collect the files first and then process them. Do not do any office related stuff between the calls to Dir.

Option Explicit

' Get the files specified
Function GetFiles(MyFolder As String) As Variant
    Dim MyFile As Variant, Files As Variant
    Dim NumFiles As Long, Idx As Long
    
    ' Collect files only and ignore folders
    MyFile = Dir(MyFolder)
    NumFiles = 0
    Do While MyFile <> ""
        NumFiles = NumFiles + 1
        If NumFiles = 1 Then
            ReDim Files(1 To 1)
        Else
            ReDim Preserve Files(1 To NumFiles)
        End If
        Files(NumFiles) = MyFile
        MyFile = Dir()
    Loop
    GetFiles = Files
End Function

Sub ProcessFiles(MyFolder As String)
    Dim MyFile As Variant
    Dim Files As Variant
    Dim Idx As Long
    
    Files = GetFiles(MyFolder)
    If Not IsEmpty(Files) Then
        For Idx = LBound(Files) To UBound(Files)
            MyFile = Files(Idx)
            ' Process the file here
            Debug.Print MyFile
        Next Idx
    Else
        Debug.Print "No files found for Dir(""" & MyFolder & """)"
    End If
End Sub

Sub TestProcessFiles()
    ProcessFiles "C:\windows\*.*"
End Sub