Answered by:

Why do Word VBA Collection.Add convert File objects to Strings?

Question

I'm trying to use a VBA Collection to store files for later calculations on the file objects. However, the objects stored in the Collection isn't Files; instead they are Variant/Strings. I have written a short demonstration of whats happening:

Sub StartSub()
Dim fso As New FileSystemObject
Dim d As Folder
Dim f As file
Dim files As New collection
Dim fileNames As collection
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFolderPicker)
fd.Title = "Select dir"
fd.Show
Set d = fso.GetFolder(fd.SelectedItems(1))
For Each f In d.files
files.Add (f)
Next f
Set filesNames = GetFileNames(files)
End Sub
Function GetFileNames(files As collection) As collection
Dim fileNames As collection
Dim file As file
For Each file In files
fileNames.Add (file.Name)
Next
Set GetFileNames = fileNames
End Function

When I run this, the files Collection in StartSub contains Variant/Strings, and when trying to loop over these in GetFileNames it fails with a runtime error 424.

I now have three questions:

1. Why isn't a File object stored in the Collection?

2. Can a File object be made to be stored in the Collection through some magic?