Dir Skipping Files

[LarryP]

Why does the following code only list three of the six .xls files in my c:\
directory?

Public Sub ListOfFiles()
Dim x As Integer, strFName As String
Debug.Print Dir("c:\*.xls")
For x = 1 To 1000
strFName = Dir
If strFName = "" Then
Exit For
Else
Debug.Print Dir()
End If
Next x

End Sub

 

[Douglas J. Steele]

You're calling Dir twice inside the loop. Try

Public Sub ListOfFiles()
Dim x As Integer, strFName As String
Debug.Print Dir("c:\*.xls")
For x = 1 To 1000
strFName = Dir
If strFName = "" Then
Exit For
Else
Debug.Print strFName
End If
Next x

End Sub

Better, though, would be

Public Sub ListOfFiles()
Dim strFName As String

strFName = Dir("c:\*.xls")
Do While Len(strFName) = 0
Debug.Print strFName
strFName = Dir()
Loop

End Sub

 

[LarryP]

Thanks as always, Doug. Love your nice tight code, but seems like the Do
While would result in nothing ever getting printed if any file is found.
Shouldn't it be Do Until?

By the way, related: this is great for as long as one is searching for a
particular filename pattern in a particular folder, but can you suggest a way
to make it iterate through subfolders as well? I'm after the same
functionality I enjoyed with Application.Filesearch, where one feeds it the
filename pattern and the starting point for the search as separate strings.
I'm thinking one needs a construct along the lines of
"c:\startingpointfolder" & "\*\" & "filenamepattern.*" but I can't quite
connect the dots.

 

[Douglas J. Steele]

Oops. You're right. I meant to use Do While Len(strFName) > 0, but Do Until
Len(strFName) = 0 will work just as well.

Sorry about that...

You might want to check an article "Find Your Data" I wrote for Access
Advisor
http://my.advisor.com/articles.nsf/aid/16279

Allen Browne has something at http://www.allenbrowne.com/ser-59.html as
well.

 

[LarryP]

Thanks again, Doug. Both of those articles look like they'll move me forward.