Collection Maximum Size
15,149
Solution 1
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.
Solution 2
How about a disconnected recordset? This thread is about VBScript, but it is very similar to VBA:
How do I sort arrays using vbscript?
Author by
Alex Gordon
Check out my YouTube channel with videos on Azure development.
Updated on June 20, 2022Comments
-
Alex Gordon almost 2 years
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?