VBA Excel get first file name from the files collection returned by GetFolder.Files

Question

I'm trying to get the first file of a directory. I don't care that "first" is not well defined in this case, and I don't care if I'll get a different file each time I call my sub.

I try to use:

Dim FSO As Object
Dim SourceFolder As Object

Dim FileItem As Object

Set FSO = CreateObject("Scripting.FileSystemObject")
Set SourceFolder = FSO.GetFolder(SourceFolderName)

Set FileItem = SourceFolder.Files.Item(0)

but this returns a compiler error ("Invalid procedure call or argument") Could you please tell me how to make this work?

Thanks, Li

Answer 1

You may use the bulit in Dir function

Below is the sample code which returns the first file found name from Test folder.

Sub test()

    Dim strFile As String
    strFile = Dir("D:Test\", vbNormal)

End Sub

Answer 2

It looks to me like SourceFolder.Files will only accept a string as the key, just like you noted with Scripting.Folders. I think Santosh's answer is the way to go, but here's a kludgy modification of your code that returns the "first" file in the folder:

Sub test()

Dim FSO As Object
Dim SourceFolder As Object
Dim FileItem As Object
Dim FileItemToUse As Object
Dim SourceFolderName As String
Dim i As Long

SourceFolderName = "C:\Users\dglancy\Documents\temp"
Set FSO = CreateObject("Scripting.FileSystemObject")
Set SourceFolder = FSO.GetFolder(SourceFolderName)

For Each FileItem In SourceFolder.Files
    If i = 0 Then
        Set FileItemToUse = FileItem
        Exit For
    End If
Next FileItem

Debug.Print FileItemToUse.Name
End Sub