Collection Maximum Size

Question

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?

Roue

Your code works perfectly fine for me with thousands of files. Are you certain that you have permission to access that folder? – Demoiselle

Sorry, I have to ask. How did you come up with that procedure name? – Footlambert

please do not ask such difficult questions – Roue

Answer 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.

Rainier

same problem with Excel 2016 and not in debug mode – Endorsed

Excellent, helpful bit of information! And VBE's Locals window is similarly limited to displaying only the first 256 items in a Collection. – Spinal

Answer 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?

Peraea

heeeeey remou its your old buddy alex – Roue