blocks|key|212154|text|您可以在Dir函数中使用bulit|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|212155|下面是示例代码，它返回从Test文件夹找到的第一个文件的名称。|212156|Sub+test()

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

End+Sub|code-block|syntax|javascript|212157|entityMap^0|4|3|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|R|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|S|8|@]|D|@]|E|$K|L]]|$1|M|3|-4|5|6|7|T|8|@]|D|@]|E|$]]]|N|$]]

You may use the bulit in <code>Dir</code> function 

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

<pre><code>Sub test()

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

End Sub
</code></pre>

blocks|key|212209|text|在我看来，就像你在Scripting.Folders中提到的那样，SourceFolder.Files只会接受一个字符串作为键。我认为Santosh的答案是可行的，但这里是对您的代码的一个笨拙的修改，它返回文件夹中的“第一个”文件：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|212210|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|code-block|syntax|javascript|212211|entityMap^0|9|H|X|I|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|R|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|S|8|@]|D|@]|E|$]]]|L|$]]

It looks to me like <code>SourceFolder.Files</code> will only accept a string as the key, just like you noted with <code>Scripting.Folders</code>. 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:

<pre><code>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
</code></pre>

blocks|key|212307|text|诚然，VBA有一个限制(我认为是错误或设计缺陷)，即文件系统对象的Files集合不能通过项目索引号访问，只能通过每个项目的文件路径字符串值访问。这里发布的原始问题是关于只访问Files集合中的第一项，但它涉及到一个一般性问题，对于这个问题有两个合理的解决方法:创建和使用File对象元集合或File对象数组，以提供对Files集合的索引访问。下面是一个演示例程：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|212308|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+%2B+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|code-block|syntax|javascript|212309|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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:

<pre><code>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 &lt;&gt; 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: " &amp; 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: " &amp; FileArr(Idx).Name
 Next Idx
End Sub
</code></pre>

blocks|key|212378|text|我是这样解决这个问题的：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|212379|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|code-block|syntax|javascript|212380|不要忘记在你的项目中添加对Microsoft脚本运行时的引用，它对我很有效……我希望这对你们有帮助。|212381|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

I solve the problem in this Way:

<pre><code>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
</code></pre>

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

blocks|key|212470|text|为什么不直接使用一个函数来遍历文件夹中的文件，直到找到你想要的文件？假设你正在使用fso，就像上面其他文章中详细描述的那样，只需传递文件夹，以及你想要的文件的索引，它可以是#1或文件夹中的任何其他文件。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|212471|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+%2B+1
++++If+Count+=+Index+Then
++++++Set+GetFile+=+oFile
++++++Exit+Function
++++End+If
++Next+oFile
End+Function|code-block|syntax|javascript|212472|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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.

<pre><code>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
</code></pre>

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:

<pre><code>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)
</code></pre>

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

Thanks,
Li

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

文件系统

编译器

我正在尝试获取目录的第一个文件。我并不关心"first“在这种情况下没有很好地定义，我也不关心我每次调用sub时是否会得到不同的文件。我试着使用：Dim FSO As ObjectDim SourceFolder As ObjectDim FileItem As ObjectSet FSO = CreateObject...

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

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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