The following function will return an array with all of the files is a given folder having a particular extension:
Code:
Option Explicit
Function FilesArray(PathStr As String, Ext As String)
' Function by Patrick Matthews
' Function looks in specified directory, and returns a zero-based array of full name and
' path for all files in that directory matching the specified extension (.???).
' Does *not* look in subfolders
' May be used in a worksheet or in code. Code examples:
' To get the number of files, use this line in a master sub or function:
' MyArr = FilesArray("C:\MyFolder", ".doc")
' NumFiles = UBound(MyArr) + 1
' To get the paths:
' MyArr = FilesArray("C:\MyFolder", ".doc")
' Debug.Print MyArr(3) 'prints fourth file's path in immediate window--remember, zero-based!
' To get the file names:
' MyArr = FilesArray("C:\MyFolder", ".doc")
' Debug.Print Mid(MyArr(3), InStrRev(MyArr(3), "\") + 1)
Dim Dict As Object 'Scripting.Dictionary
Dim fso As Object 'Scripting.FileSystemObject
Dim Fld As Object 'Scripting.Folder
Dim Fil As Object 'Scripting.File
Dim arr As Variant
Set fso = CreateObject("Scripting.FileSystemObject")
Set Fld = fso.GetFolder(PathStr)
Set Dict = CreateObject("Scripting.Dictionary")
For Each Fil In Fld.Files
If UCase(Right(Fil.Name, Len(Ext))) = UCase(Ext) Then Dict.Add Fil.path, Fil.path
Next
arr = Dict.Keys
Set Dict = Nothing
Set Fil = Nothing
Set Fld = Nothing
Set fso = Nothing
FilesArray = arr
End Function
So now, use that function to populate an array with your files, and loop through the array invoking TransferSpreadsheet.