Here's my code:
Sub isdofsodjisf48023jroi23f984444444jiodfiosj12348023jroi23f98()
Dim colFiles As New Collection
RecursiveDir colFiles, "C:\Documents and Settings\Alex Gordon\Desktop\testing\files\", "*.xls", True
Dim vFile As Variant
For Each vFile In colFiles
Call writeincells(vFile)
Next vFile
End Sub
Public Function RecursiveDir(colFiles As Collection, _
strFolder As String, _
strFileSpec As String, _
bIncludeSubfolders As Boolean)
Dim strTemp As String
Dim colFolders As New Collection
Dim vFolderName As Variant
'Add files in strFolder matching strFileSpec to colFiles
strFolder = TrailingSlash(strFolder)
strTemp = Dir(strFolder & strFileSpec)
Do While strTemp <> vbNullString
colFiles.Add strFolder & strTemp
strTemp = Dir
Loop
If bIncludeSubfolders Then
'Fill colFolders with list of subdirectories of strFolder
strTemp = Dir(strFolder, vbDirectory)
Do While strTemp <> vbNullString
If (strTemp <> ".") And (strTemp <> "..") Then
If (GetAttr(strFolder & strTemp) And vbDirectory) <> 0 Then
colFolders.Add strTemp
End If
End If
strTemp = Dir
Loop
'Call RecursiveDir for each subfolder in colFolders
For Each vFolderName In colFolders
Call RecursiveDir(colFiles, strFolder & vFolderName, strFileSpec, True)
Next vFolderName
End If
End Function
Public Function TrailingSlash(strFolder As String) As String
If Len(strFolder) > 0 Then
If Right(strFolder, 1) = "\" Then
TrailingSlash = strFolder
Else
TrailingSlash = strFolder & "\"
End If
End If
End Function
I am filling up a Collection with a list of filenames in a directory structure.
I have 2000 files, but the Collection only return 256. Does anyone know if there is a maximum number that it won't go past?
If so, can you please suggest a better way to code this macro so that it captures all 2000 files?
The code is working OK in Excel 2007. Perhaps what is happening is that you are trying to Watch the Collection in Debug mode. The Debugger shows only the first 256 items.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With