Dir() and FSO Both Missing 1 File

Question

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).

'I do not need to exclude files from a search within a folder, but include them all.' ... but in your Dir() method you are restricting it to .xls files. So it would it be fair to assume your goal is to see every single .xls file only, and your problem is that 1 of your .xls isn't showing at random? — mrlemmer11, Aug 19 '15 at 19:34
Yes, all files are .xls or .xlsx and 1 of them at random does not show up. — Mike, Aug 19 '15 at 19:35
Nelly, the Dir() in the FSO version is there to set SourceFile to the next file in the directory. — Mike, Aug 19 '15 at 19:40
I can only replicate this when I make a file hidden in my directory. Other than that, I can't seem to see what the issue is. — mrlemmer11, Aug 19 '15 at 19:42
It might be interesting to see if you have the problem with a completely different programming language. For example this question gives a Python snippet you can try: http://stackoverflow.com/q/3964681/4996248 If you get the problem there then the problem lies with your OS — John Coleman, Aug 19 '15 at 19:45
Nelly your question helped me solve the issue with the FSO version, which is that SourceFile = Dir() was setting the variable to the next file in the list before it set the value to the active cell, thus skipping the first file each time. Though this doesn't appear to be the case in the Dir() version. — Mike, Aug 19 '15 at 19:57

Mike · Accepted Answer · 2015-08-19T20:52:34.760

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

Dir() and FSO Both Missing 1 File

1 Answers1