如果我们要在Excel中获取某个文件夹中所有的文件列表,可以通过下面的VBA代码来进行。代码运行后,首先弹出一个浏览文件夹对话框,然后新建一个工作簿,并在工作表的A至F列分别列出选定文件夹中的所有文件的文件名、文件大小、创建时间、修改时间、访问时间及完整路径。方法如下: 1.按Alt+F11,打开VBA编辑器,单击菜单“插入→模块”,将下面的代码粘贴到右侧的代码窗口中: Option Explicit Dim strFolder As String '显示打开文件夹对话框 '获取文件夹中的所有文件列表 If Not IsArray(varFileList) Then '获取文件的详细信息,并放到数组中
Set FSO = CreateObject("Scripting.FileSystemObject")
Private Function fcnGetFileList(ByVal strPath As String, Optional strFilter As String) As Variant Dim f As String If strFilter = "" Then strFilter = "*.*" Select Case Right$(strPath, 1) ReDim Preserve FileList(0) f = Dir$(strPath & "/" & strFilter) If FileList(0) <> Empty Then Dim iSheetsInNew As Integer If mySh Is Nothing Then '新建一个工作簿 Else Set mySh = sh End If With sh Range(.Cells(1, 1), .Cells(UBound(varData, 1) + 1, UBound(varData, 2) + 1)) = varData End With Set sh = Nothing End Sub 2.关闭VBA编辑器,回到Excel工作表中,按Alt+F8,打开“宏”对话框,选择“GetFileList”,单击“运行”按钮。 |
请问:VBA中如何取文件的最后修改时间?
已经解决了,新的代码
---------------------------------------------
Sub searchfiles()
With Application.FileSearch
.NewSearch
.LookIn = "D:/ttt"
.Filename = "*.xls"
.SearchSubFolders = True
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
Worksheets("sheet3").Cells(i, 2).Value = .FoundFiles(i)
Dim fs, f, s
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(.FoundFiles(i))
s = "Created: " & f.DateCreated
Worksheets("sheet3").Cells(i, 3).Value = s
Set f = Nothing
Set fs = Nothing
Next i
Else
MsgBox "no file found."
End If
End With
End Sub