VBA - alternative to Set dotnetarray = CreateObject("System.Collections.ArrayList") as this needs .net 3.5

[miless2111s]

Situation:
I have a macro which reads a list of files into an array and then performs operations on each file in turn. As I don't know the size of the array at the start and want to be able to easily add and remove items to the array I have used
Set dotnetarray = CreateObject("System.Collections.ArrayList")

Problem:
This macro works brilliantly on my works machine however it errors on this line on my home machine which is running more up-to-date software. Having done a little reading around it appears that this may be due to the fact that this method needs Net 3.5 to be installed.

Request:
As I am going to issue this code to many users and can't ask them to mess about the .net etc I need a method that will work regardless of bits, .net etc

This is the code in full:

Sub RenameMyFiles_all_files()

'source: https://debugvba.com/rename-multiple-files-in-a-folder-or-directory-by-vba-excel-debugvba-com/
' source for ArrayList which allows easy addition of items to an array: https://www.tachytelic.net/2019/09/vba-add-item-array/#:~:text=VBA%20does%20not%20allow%20you,then%20add%20the%20required%20items.&text=There%20are%20a%20number%20of,time%20it%20is%20re%2Ddimensioned.
Dim objFileSystem As Object
Dim SourceFolder As String
Dim OriginalFile, RenamedFile As String
Dim MyPrefix, MySuffix As String
Dim wb As String
Dim Existing_file_names() As String
Dim msg_string As String
Dim name1 As Variant
Dim name2 As Variant
Dim replacement_name As String

wb = ThisWorkbook.Name


MySuffix = Workbooks(wb).Worksheets("sheet1").Range("Suffix").Value
latestfilename = Workbooks(wb).Worksheets("sheet1").Range("CurrentName").Value

'Path of the folder where files are locted
SourceFolder = Workbooks(wb).Worksheets("sheet1").Range("OriginPath").Value 'Range("OriginName").Value
   
Set objFileSystem = CreateObject("Scripting.FileSystemObject")
Set dotnetarray = CreateObject("System.Collections.ArrayList")
Set DotNetArray2 = CreateObject("System.Collections.ArrayList")

'Check if source and target folder exists
If objFileSystem.FolderExists(SourceFolder) = True Then

    'Looping through each .xlsx file in the source folder
    For Each OriginalFile In objFileSystem.getfolder(SourceFolder).Files
   
      'Check if selected file is an excel or visio file
      If InStr(OriginalFile, ".xlsm") Or InStr(OriginalFile, ".vsdx") Then
        fileName = OriginalFile.Name
        filetype = Right(fileName, Len(fileName) - InStr(fileName, "."))
        fileName = Left(fileName, InStr(fileName, ".") - 1)
        'avoid renaming the latest file which will be used as a basis for mass transfer macro
        If fileName = latestfilename Then
            'do nothing with this file
        Else
          MyFileLocation = objFileSystem.GetParentFolderName(OriginalFile)
          RenamedFile = MyFileLocation & "\" & MyPrefix & fileName & MySuffix & "." & filetype
          If IsFile(RenamedFile) Then 'returns true if the file exists and so we can't rename to avoid a name clash
              'put the file name into an array and re-run after all the other files have been rename
               dotnetarray.Add OriginalFile.Name
          Else
               'rename the file
              Name OriginalFile As RenamedFile
          End If
        End If
      End If
    Next OriginalFile
   
    're-run the process to pick up file names that couldn't be handled the first time
    If dotnetarray.Count > 0 Then ' NEED TO WORK OUT HOW TO CYCLE THROUGH THE FILE NAMES AND THE CONTENTS OF THE DIRECTORY
        'Looping through each .xlsx file in the source folder
        For Each OriginalFile In objFileSystem.getfolder(SourceFolder).Files
       
            'Check if selected file is in the DotNetArray list
            If dotnetarray.contains(OriginalFile.Name) Then
                fileName = OriginalFile.Name
                filetype = Right(fileName, Len(fileName) - InStr(fileName, "."))
                fileName = Left(fileName, InStr(fileName, ".") - 1)
               
                MyFileLocation = objFileSystem.GetParentFolderName(OriginalFile)
                RenamedFile = MyFileLocation & "\" & MyPrefix & fileName & MySuffix & "." & filetype
                If IsFile(RenamedFile) Then 'returns true if the file exists and so we can't rename to avoid a name clash
                    'put the file name into an array and re-run after all the other files have been rename
                     DotNetArray2.Add OriginalFile.Name
                Else
                    'rename the file
                    Name OriginalFile As RenamedFile
                    replacement_name = fileName & "." & filetype
                    dotnetarray.Remove replacement_name
                End If
           
            End If
        Next OriginalFile
    End If

Else
  MsgBox "Source folder does not exist"
  Exit Sub
End If

If DotNetArray2.Count > 0 Then
    msg_string = ""
    For Each name2 In dotnetarray
        msg_string = msg_string & ", " & name2
    Next
msg_string = Right(msg_string, Len(msg_string) - 2)
End If


If DotNetArray2.Count > 0 Then
    MsgBox "All done, note that the following files could not be renamed as their renamed file names already exist: " & msg_string
Else
    MsgBox "all done"
End If

End Sub

 

[miless2111s]

nothing?
I assume that collections could be an answer however I haven't used them before so I am not clear...

 

[John_w]

Yes, you could use Collections, because they are standard VBA data structures, not an external library.

Replace this part of your code:

Set dotnetarray = CreateObject("System.Collections.ArrayList")
Set DotNetArray2 = CreateObject("System.Collections.ArrayList")

with:

    Dim filesColl As Collection, files2Coll As Collection
    Set filesColl = New Collection
    Set files2Coll = New Collection

and change all the remaining dotnetarray and DotNetArray2 references to filesColl and files2Coll respectively.

The one thing lacking in a Collection is the contains method, which you use here:

            If dotnetarray.contains(OriginalFile.Name) Then

We can add a function to achieve the same thing:

Private Function CollectionContainsItem(coll As Collection, item As Variant) As Boolean
    Dim i As Long
    CollectionContainsItem = False
    For i = 1 To coll.Count
        If coll(i) = item Then
            CollectionContainsItem = True
            Exit For
        End If
    Next
End Function

And call it like this (replacing your line above):

            If CollectionContainsItem(filesColl, OriginalFile.Name) Then

NB. In this part of your code, shouldn't dotnetarray be DotNetArray2?

If DotNetArray2.Count > 0 Then
    msg_string = ""
    For Each name2 In dotnetarray
        msg_string = msg_string & ", " & name2
    Next
msg_string = Right(msg_string, Len(msg_string) - 2)
End If