Solved: Test for empty directory - odd result

[TheAntiGates]

I grabbed this Rob Bovey code (slightly tightened)
Function bIsEmpty(ByVal szPath As String) As Boolean
Dim szTemp As String
bIsEmpty = True
If Right$(szPath, 1) <> "\" Then szPath = szPath & "\"
szTemp = Dir$(szPath & "*.*")
If szTemp <> "" Then bIsEmpty = False
End Function
and invoke with
If Not bIsEmpty(CurDir)

However, it is failing. szTemp is getting the name of a file that I deleted earlier. There are no hidden or other attribute files in that dir whatsoever except for . and ..
(I'm assuming that ?curdir in the Immediate window is accurate?)

Restarting Excel solved it. Is this for real? Does VB cache directory contents??? Version XL03.

[TheAntiGates]

Also, I ran a test with a single dummy file in the directory. VBA help for Dir command says, as I read it, that I can successively go dir("") to read every file in the dir. But as it constantly returns the same filename, how do you know when you're done reading the files in the dir? Do I need to save off the prior result and test against it each time?!

I tried again with 2 files in the dir. Because of the above post and seeming (albeit insane) "caching" I restarted XL03. And dir$ never reports the 2nd file's name. (edit: I.e. it repeatedly reports the first file found; it always gets the same thing it got for Dir$(szPath & "*.*"). ) I'm wondering if the help file is incorrect or what.

[Kenneth Hobs]

Notice the use of Dir() with setting a parameter.


'http://spreadsheetpage.com/index.php/tip/getting_a_list_of_file_names_using_vba/
Sub Test_GetFileList()
Dim p As String, x As Variant, i As Integer

p = ThisWorkbook.Path & "/*.xls"
x = GetFileList(p)
Select Case IsArray(x)
Case True 'files found
MsgBox UBound(x)
Sheets("Sheet1").Range("A:A").Clear
For i = LBound(x) To UBound(x)
Sheets("Sheet1").Cells(i, 1).Value = x(i)
Next i
Case False 'no files found
MsgBox "No matching files"
End Select
End Sub

Function GetFileList(FileSpec As String) As Variant
' Returns an array of filenames that match FileSpec
' If no matching files are found, it returns False

Dim FileArray() As Variant
Dim FileCount As Integer
Dim FileName As String

On Error GoTo NoFilesFound

FileCount = 0
FileName = Dir(FileSpec)
If FileName = "" Then GoTo NoFilesFound

' Loop until no more matching files are found
Do While FileName <> ""
FileCount = FileCount + 1
ReDim Preserve FileArray(1 To FileCount)
FileArray(FileCount) = FileName
FileName = Dir()
Loop
GetFileList = FileArray
Exit Function

' Error handler
NoFilesFound:
GetFileList = False
End Function

[TheAntiGates]

Very good. Dir() did solve the second question. I guess Microsoft's Dir function help screen writer smoked some pretty good weed.

As I don't expect anyone to respond to the first question I'll tag this one as a victory. Thanks very much. :clap: