vba 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 names of filenames in a directory structure

i have 2000 files and the collection only return 256. does anyone know if there is a maximum number that it wont go past?

if so,. can you please suggest a better way to do this macro so that it captures all 2000 files?

Answer 1

Instead of using Collection, use List(of String) - that should make it work.

Answer 2

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.

Answer 3

How about a disconnected recordset? This thread is about VBScript, but it is very similar to VBA:

http://stackoverflow.com/questions/268648/how-do-i-sort-arrays-using-vbscript/308735#308735