CODEWITHSUNDEEP
excelvbadirfso
Mike
Mike

Reputation: 153

Dir() and FSO Both Missing 1 File

All search attempts to find a solution to this issue have come up with the opposite of what I'm looking for. I do not need to exclude files from a search within a folder, but include them all.

My issue is that my searches are returning all files within the folder except 1. The 1 file that is not found each time is completely random. I have tried using both Dir() and FSO methods, different directories, different number of files, etc. No matter what I try, 1 file is always missing from the list.

Here are simplified snippets of my code:

Dir() version:

FilePath = "C:\Test\"

SourceFile = Dir(FilePath & "*.xls*")

Do While SourceFile <> "" 
   SourceFile = Dir()
   ActiveCell.Value = SourceFile
   ActiveCell.Offset(1, 0).Activate
Loop

FSO version:

Set FileSystem = CreateObject("Scripting.FileSystemObject")
DoFolder FileSystem.GetFolder(FilePath)

Sub DoFolder(Folder)
    Dim SubFolder
    For Each SubFolder In Folder.SubFolders
        DoFolder SubFolder
    Next

    Dim File
    For Each File In Folder.Files
        If File.Name <> "" Then
            SourceFile = Dir()
            ActiveCell.Value = SourceFile
            ActiveCell.Offset(1, 0).Activate
        End If
    Next
End Sub

Again, these both return all of the files except 1 (at random).

Upvotes: 2

Views: 975

Answers (1)

Mike
Mike

Reputation: 153

In both versions, SourceFile = Dir() was above the ActiveCell.Value = SourceFile. This caused the first file to be missed by skipping to the next file in the list prior to adding the file name to the list.

Corrected code:

Dir() version:

FilePath = "C:\Test\"

SourceFile = Dir(FilePath & "*.xls*")

Do While SourceFile <> ""   
   ActiveCell.Value = SourceFile
   ActiveCell.Offset(1, 0).Activate
   SourceFile = Dir()
Loop

FSO version:

Set FileSystem = CreateObject("Scripting.FileSystemObject")
DoFolder FileSystem.GetFolder(FilePath)

Sub DoFolder(Folder)
    Dim SubFolder
    For Each SubFolder In Folder.SubFolders
        DoFolder SubFolder
    Next

    Dim File
    For Each File In Folder.Files
        If File.Name <> "" Then                
            ActiveCell.Value = File.Name
            ActiveCell.Offset(1, 0).Activate
        End If
    Next
End Sub

Upvotes: 4

Related Questions