VBA: Remove objects from object collection

Question

I want an Access application to autimatically import the right files from a certain folder. My idea to do this, is to get all files in the folder and then proceed with removing the wrong files from the collection. At the end, I start the import.

The problem is the removing part, VBA doesn't know the remove method in this context.

Here is an exemplary code:

Dim objFS As Object
Dim objFolder As Object
Dim objFiles As Object
Dim objF1 As Object

Set objFS = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFS.GetFolder(strPath)
Set objFiles = objFolder.Files

' remove files with wrong YearMonth from collection
For Each objF1 In objFiles
    If Left(Right(objF1.Name, 8), 6) <> YearMonth Then
        ' the following line causes the error
        objFiles.Remove (objF1.Name) 
    End If
Next

Is there no option to simply remove an object from objFiles?

If not, I guess I would just populate another array, store all objF1 I want to remove and us the array as an exclude-filter for the actual file import.

edit: Seems like I have to go with the array solution. Thanks for the help.

edit2: I went for a string array, stores all names of files I don´t want to import. Final import method checks for names in this list.

Answer 1

This question is mega-old so I'm just answering this for posterity.

So, the reason one might not want to go the arraylist route is because GetFolder returns a collection of type File (class). The File class can do lots of cool things and we might want to hold on to that functionality. This example uses early binding, however the principals are the same and it can be applied to either case.

   Dim oFSO As Scripting.FileSystemObject
   Dim oFolder As Scripting.Folder
   Dim oFiles As Scripting.Files
   Dim oFile As Scripting.File
   Dim oFileNameList As Collection

   Set oFSO = New Scripting.FileSystemObject
   Set oFolder = oFSO.GetFolder("C:\YourDirectoryHere")
   Set oFiles = oFolder.Files
   For Each oFile In oFiles
       'This is what I'm using to filter out the specific file types i'm looking for.  
       'This can be whatever criterion you want to use.  Either way, what you're doing
       'is creating your own collection of Scripting.File objects.  You could also
       'exclude the filtering part here and just add them all directly to the collection
       'so that you can do what you need to at a later part of the code.
       If UCase(oFSO.GetExtensionName(oFile.Name)) = "XLSX" Then oFileNameList.Add oFile  
   Next oFile

At this point, oFileNameList is a Collection of multiple Scripting.File objects which is essentially what Scripting.Files is anyway. However the main difference is that you can add/remove files from this collection as needed. The individual objects in this collection can still be accessed individually as their original type. So you could still do something like oFileNameList.Item(1).Attributes = ReadOnly if you wanted to. Or, if using a For Each loop, oFile.Attributes = ReadOnly.

Keeping the functionality of Scripting.File objects can be useful and necessary depending on what you're doing. It can also be kinda dangerous if you're not paying attention.

Answer 2

The GetFolder methode returns an instance of FileCollection. According the docs, there is no way to remove objects from this collection. You might want to consider GetFolder as a solely informational method.

So if you want to stick to your remove-the-processed-files approach, you will have to make a copy to a mutable VBA.Collection upfront and work with that.

Answer 3

You can't modify collection if You work on this collection. You can make new list where You can add good files. For example:

List<int> listOfNumbers = new List<int>();

        for (int i = 0; i < 11; i++)
        {
            listOfNumbers.Add(i);
        }

        //The above collection includes 0-10 number but You need only even numbers

        //Your current option - not work because You can't modify collection used in foreach
        foreach (var item in listOfNumbers)
        {
            if (item % 2 == 1)
                listOfNumbers.Remove(item);
        }

        //Correct solution
        List<int> evenNumbers = new List<int>();
        foreach (var item in listOfNumbers)
        {
            if (item % 2 == 0)
                evenNumbers.Add(item);
        }

Answer 4

What about an ArrayList?

Sub SO()

strPath$ = "C:\Users\olearysa\desktop\"
MonthYear$ = "DateFi"

With CreateObject("System.Collections.ArrayList")

    fileName = Dir(strPath & "*.*", vbNormal)

    While Not fileName = vbNullString
        If Len(fileName) > 12 Then
            If Mid(fileName, InStrRev(fileName, ".") - 8, 6) = MonthYear Then .Add strPath & fileName
        End If
    fileName = Dir()
Wend

Debug.Print .count

For i = 0 To .count - 1
    '// Example
    Workbooks.Open .Item(i)
    '// more code here...
Next i

.Clear

End With

End Sub