Excel VBA 使用 FileSystemObject 列出最后修改日期的文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16627441/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA using FileSystemObject to list file last date modified
提问by user2397403
this is my first time asking question so hopefully I'm following protocol. This is in reference to "get list of subdirs in vba" get list of subdirs in vba.
这是我第一次提问，所以希望我遵循协议。这是参考“获取 vba中的子目录列表”获取vba中的子目录列表。
I found Brett's example #1 - Using FileScriptingObject most helpful. But there's one more data element (DateLastModified) I need in results. I tried to modify the code but keep getting invalid qualifier error. Here are code modifications I made:
我发现 Brett 的示例 #1 - Using FileScriptingObject 最有帮助。但是我在结果中还需要一个数据元素 (DateLastModified)。我试图修改代码，但不断收到无效的限定符错误。以下是我所做的代码修改：
- Range("A1:C1") = Array("File Name", "Path", "Date Last Modified").
- Do While loop added this => Cells(i, 3) = myFile.DateLastModified.
- Range("A1:C1") = Array("文件名"、"路径"、"上次修改日期")。
- Do While 循环添加了这个 => Cells(i, 3) = myFile.DateLastModified。
Will appreciate help to include the "Date Last Modified".
将感谢包含“上次修改日期”的帮助。
Santosh here is complete code with comments indicating modifications.
Santosh 这里是完整的代码，带有表示修改的注释。
Public Arr() As String
Public Counter As Long
Sub LoopThroughFilePaths()
Dim myArr
Dim i As Long
Dim j As Long
Dim MyFile As String
Const strPath As String = "c:\temp\"
myArr = GetSubFolders(strPath)
Application.ScreenUpdating = False
'Range("A1:B1") = Array("text file", "path")' <= orig code
Range("A1:C1") = Array("text file", "path", "Date Last Modified") ' <= modified code
For j = LBound(Arr) To UBound(Arr)
MyFile = Dir(myArr(j) & "\*.txt")
Do While Len(MyFile) <> 0
i = i + 1
Cells(i, 1) = MyFile
Cells(i, 2) = myArr(j)
Cells(i, 3) = MyFile.DateLastModified ' <= added to modify code
MyFile = Dir
Loop
Next j
Application.ScreenUpdating = True
End Sub
Function GetSubFolders(RootPath As String)
Dim fso As Object
Dim fld As Object
Dim sf As Object
Dim myArr
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(RootPath)
For Each sf In fld.SubFolders
Counter = Counter + 1
ReDim Preserve Arr(Counter)
Arr(Counter) = sf.Path
myArr = GetSubFolders(sf.Path)
Next
GetSubFolders = Arr
Set sf = Nothing
Set fld = Nothing
Set fso = Nothing
End Function
回答by Santosh
Try this code :
试试这个代码：
Sub ListFilesinFolder()
Dim FSO As Scripting.FileSystemObject
Dim SourceFolder As Scripting.Folder
Dim FileItem As Scripting.File
SourceFolderName = "C:\Users\Santosh"
Set FSO = New Scripting.FileSystemObject
Set SourceFolder = FSO.GetFolder(SourceFolderName)
Range("A1:C1") = Array("text file", "path", "Date Last Modified")
i = 2
For Each FileItem In SourceFolder.Files
Cells(i, 1) = FileItem.Name
Cells(i, 2) = FileItem
Cells(i, 3) = FileItem.DateLastModified
i = i + 1
Next FileItem
Set FSO = Nothing
End Sub
