I conclude from your sample code that you are using Access 2007 (it’s the
xlsx extension that hints at Office 2007). I have just tested repeated Dir
calls without arguments in the same environment and it works for me; I get
successive files returned as described in the documentation. I can’t think
why you are getting multiple occurrences of the first file name. Remember to
enumerate from 1 (0?) to filecount each time you call the procedure.
I haven’t reviewed your later code with all the MsgBox statements. BTW
learn how to debug within the VBA coding window; it’s more flexible and you
don’t have to remember to delete or comment-out all those MsgBox statements.
What I don’t particularly like about the original procedure is the fact that
some of the file name pattern is hard coded. Surely to be more flexible the
calling routine should pass the file name pattern to the procedure. I also
don’t like the fact that it creates and redimensions the array during
initialization. What if there are many thousands of matching files?
My other criticisms fall into the category of personal preferences and
style. I wouldn’t have one general purpose procedure catering for all
actions; rather I would have a module with many procedures, each devoted to a
specific action. Suppose the module was called ‘RetrieveFileNames’ and it
contained procedures named ‘Initialize,’ ‘Count,’ ‘Next,’ etc. The calling
syntax is easier to read: ‘RetrieveFileNames.Initialize(),’
‘RetrieveFileNames.Count(),’ ‘RetrieveFileNames.Next(),’ etc. Moreover you
can be more precise about data typing with separate procedures.
I don’t like the use of arrays in this context; I would tend to use a VBA
collection or, if the number of files is small, return a string of all file
names suitably denominated for the combo box. Using redimensioned arrays you
need to scan the directory twice. With collections or a formatted string you
only need to scan the directory once.
I would not attempt to pass an Access Control object to my procedure. Well
yes I would if I wrote an interface Class Module and coded implementation
Class Modules for every likely control. In this case they are combo box and
list box and the resultant code is probably the same – but it’s the principle
of the thing!
OK, that’s enough pontificating. What I will do here is to make necessary
changes (what I think are necessary changes) to the original procedure and
then give some sample calling procedures. Beware I have not tested this!
Public Function ListFiles(lngAction As Long, _
Optional strFileNameTemplate As Variant, _
Optional lngRow As Variant) As Variant
Static strDirectoryPattern As String
Static strFileName() As String
Static lngFileCount As Long
Dim strTempFileName As String
Dim i As Long
Select Case lngAction
Case acLBInitialize
If IsMissing(strFileNameTemplate) Then
'insert error handling here - missing string
ListFiles = 0
Exit Function
End If
strDirectoryPattern = Nz(CStr(strFileNameTemplate), " ")
lngFileCount = 0
strTempFileName = Dir(strDirectoryPattern)
If strTempFileName = "" Then Exit Function
Do Until strTempFileName = ""
lngFileCount = lngFileCount + 1
strTempFileName = Dir()
Loop
ReDim strFileName(lngFileCount)
strFileName(0) = Dir(strDirectoryPattern)
For i = 1 To lngFileCount
strFileName(i) = Dir()
'could test for "" here - never happens
Next
ListFiles = lngFileCount
Case acLBGetRowCount
ListFiles = lngFileCount
Case acLBGetValue
If lngRow Is missing Then
'insert error handling
ListFiles = ""
Exit Function
End If
ListFiles = strFileName(Nz(CLng(lngRow), 0))
Case acLBEnd
Erase strFileName
End Select
End Function
Here are some sample calls.
lngFileCount = CLng(ListFiles(acLBInitialize,
“C:/Users/Snoopy/Documents/*.xlsxâ€))
lngFileCount = CLng(ListFiles(acLBGetRowCount))
varDummy = ListFiles(acLBEnd)
strRowSource = “â€
For lngCounter = 0 to CLng(ListFiles(acLBGetRowCount))
strRowSource = strRowSource & CStr(ListFiles(acLBGetValue,,lngCounter)) &
“;â€
next
Hope you had a good sleep.
Rod
