VBA Excel 从 GetFolder.Files 返回的文件集合中获取第一个文件名

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

提问by user429400

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

谢谢，李

采纳答案by Doug Glancy

It looks to me like SourceFolder.Fileswill 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:

在我看来，SourceFolder.Files它只接受一个字符串作为键，就像你用Scripting.Folders. 我认为 Santosh 的答案是要走的路，但这里有一个笨拙的代码修改，它返回文件夹中的“第一个”文件：

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

回答by Santosh

You may use the bulit in Dirfunction

您可以使用 bulit inDir函数

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

下面是返回从 Test 文件夹中找到的第一个文件名称的示例代码。

Sub test()

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

End Sub

回答by pstraton

It's true that VBA has a limitation (a bug or design flaw in my opinion) in which a file system object's Files collection cannot be accessed by item-index number, only by each item's file-path string value. The original question posted here is about accessing only the first item in the Files collection but it touches on a general problem for which there are two reasonable workarounds: creation and use of either a File object meta-collection or a File object array to provide indexed access to a Files collection. Here's a demo routine:

VBA 确实有一个限制（在我看来是一个错误或设计缺陷），其中文件系统对象的 Files 集合不能通过项目索引号访问，只能通过每个项目的文件路径字符串值访问。此处发布的原始问题是关于仅访问 Files 集合中的第一项，但它涉及一个一般问题，对此有两种合理的解决方法：创建和使用 File 对象元集合或 File 对象数组以提供索引访问文件集合。这是一个演示例程：

Sub DemoIndexedFileAccess()
    '
    'Demonstrates use of both a File object meta-collection and a File object array to provide indexed access
    'to a Folder object's Files collection.
    '
    'Note that, in both examples, the File objects being accessed refer to the same File objects as those in
    'the Folder object's Files collection.  (i.e. if one of the physical files gets renamed after the creation
    'of the Folder object's Files collection, all three sets of File objects will refer to the same, renamed
    'file.)
    '
    'IMPORTANT: This technique requires a reference to "Microsoft Scripting Runtime" be set.
    '
    '**********************************************************************************************************

    'File-selector dialog contsants for msoFileDialogFilePicker and msoFileDialogOpen:

    Const fsdCancel As Integer = 0       'File dialog Cancel button
    Const fsdAction As Integer = -1      'File dialog Action button, and its aliases...
    Const fsdOpen   As Integer = fsdAction
    Const fsdSaveAs As Integer = fsdAction
    Const fsdOK     As Integer = fsdAction

    Dim FD          As FileDialog
    Dim File        As Scripting.File
    Dim FileArr()   As Scripting.File
    Dim FileColl    As New Collection
    Dim Folder      As Scripting.Folder
    Dim FSO         As Scripting.FileSystemObject
    Dim Idx         As Integer

    'Get a folder specification from which files are to be processed

    Set FD = Application.FileDialog(msoFileDialogFolderPicker)  'Create the FolderPicker dialog object
    With FD
        .Title = "Select Folder Of Files To Be Processed"
        .InitialFileName = CurDir

        If .Show <> fsdOK Then Exit Sub
    End With

    'Use the folder specification to create a Folder object.

    Set FSO = New Scripting.FileSystemObject
    Set Folder = FSO.GetFolder(FD.SelectedItems(1))

    'A Folder object's Files collection can't be accessed by item-index number (only by each item's file-path
    'string value), so either...

    '1. Create a generic "meta-collection" that replicates the Files collection's File objects, which allows
    '   access by collection-item index:

        For Each File In Folder.Files
            FileColl.Add File
        Next File

        '"Process" the files in (collection) index order

        For Idx = 1 To FileColl.Count
            Debug.Print "Meta-Collection: " & FileColl(Idx).Name
        Next Idx

    '2. Or, create an array of File objects that refer to the Files collection's File objects, which allows
    '   access by array index:

        ReDim FileArr(1 To Folder.Files.Count)

        Idx = 1
        For Each File In Folder.Files
            Set FileArr(Idx) = File
            Idx = Idx + 1
        Next File

        '"Process" the files in (array) index order

        For Idx = LBound(FileArr) To UBound(FileArr)
            Debug.Print "File Object Array: " & FileArr(Idx).Name
        Next Idx
End Sub

回答by Genaro Gami?o Sánchez

I solve the problem in this Way:

我以这种方式解决问题：

Private Function GetFirstFile(StrDrive as String) As String

    'Var Declarations
        Dim Fso As Object, Drive As Object, F As File

    'Create a reference to File System Object and Drive
        Set Fso = New Scripting.FileSystemObject
        Set Drive = Fso.GetDrive(StrDrive)
        If Not Drive Is Nothing Then
            'Scan files in RootFolder.files property of then drive object
            For Each F In Drive.RootFolder.Files
                Exit For
            Next
            'if there are any file, return the first an get then name
            If Not F Is Nothing Then FirstFile = F.Name: Set F = Nothing
            Set Drive = Nothing
        End If
        Set Fso = Nothing

End Function

Don′t forget add Reference to Microsoft Scripting Runtime in your project It works to me... I hope this Help you guys.

不要忘记在您的项目中添加对 Microsoft Scripting Runtime 的引用它对我有用...我希望这对你们有帮助。

回答by user8068006

Why don't you just use a function to iterate through the files in the folder until you get to the one that you want? Assuming you're using the fso as detailed in other posts above, just pass the Folder, and the Index of the file you want, it could be #1 or any other file in the folder.

为什么不使用一个函数来遍历文件夹中的文件，直到找到您想要的文件？假设您正在使用上面其他帖子中详述的 fso，只需传递文件夹和所需文件的索引，它可以是 #1 或文件夹中的任何其他文件。

Function GetFile(oFolder As Folder, Index As Long) As File
Dim Count As Long
Dim oFile As File
  Count = 0
  For Each oFile In oFolder.Files
    Count = Count + 1
    If Count = Index Then
      Set GetFile = oFile
      Exit Function
    End If
  Next oFile
End Function