学习Files集合。只能用For each 不能用for ii= 1 to nn

ning84 · 发表于 2024-10-10 10:49

本帖最后由 ning84 于 2024-10-10 11:17 编辑

Files 集合 描述

在一个文件夹内的所有 File 对象的集合。

说明

下面的代码举例说明了如何获得一个 Files 集合，以及如何用 For Each...Next 语句来访问这个集合中的每个File：

Sub ShowFolderList(folderspec)
Dim fs, f, f1, fc, s
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(folderspec)
Set fc = f.Files
For Each f1 in fc
s = s & f1.name
s = s & vbCrLf
Next
MsgBox s
End Sub

复制代码

Private Sub ShtNameFolderTraverseFile()
Dim Str
Dim Fso As Scripting.FileSystemObject
Set Fso = New Scripting.FileSystemObject
Dim oFile As Scripting.File, oFolder As Folder
Dim Rng As Range
Set Rng = Selection
Dim Sht As Worksheet
Set Sht = Rng.Parent
Str = ThisWorkbook.Path & "" & Sht.Name
Set oFolder = Fso.GetFolder(Str)
Dim Rr
Rr = 10
Set oFile = oFolder.Files(12)
Debug.Print oFile.Name
Stop
For ii = 1 To oFolder.Files.Count
Debug.Print oFile.Name, oFile.Path
Next ii
End Sub

复制代码

Private Sub SelectFolderToSheet()
Dim Str
Dim Fso As Scripting.FileSystemObject
Set Fso = New Scripting.FileSystemObject
Dim oFile As Scripting.File, oFolder As Folder
Dim FileDia As FileDialog
Set FileDia = Application.FileDialog(msoFileDialogFolderPicker)
With FileDia
.AllowMultiSelect = True
.Show
'.InitialFileName = "C:"
Str = .SelectedItems(1)
End With
Set oFolder = Fso.GetFolder(Str)
Debug.Print oFolder.Name, oFolder.Path
Dim Rng As Range
Set Rng = Selection
Dim Sht As Worksheet
Set Sht = Rng.Parent
Sht.Name = oFolder.Name
Dim Rr
Rr = 10
For ii = 1 To oFolder.Files.Count
Set oFile = oFolder.Files(ii)
Debug.Print oFile.Name, oFile.Path
Next ii
End Sub
Private Sub ShtNameFolderTraverseFile()
Dim Str
Dim Fso As Scripting.FileSystemObject
Set Fso = New Scripting.FileSystemObject
Dim oFile As Scripting.File, oFolder As Folder
Dim Rng As Range
Set Rng = Selection
Dim Sht As Worksheet
Set Sht = Rng.Parent
With Sht
.Cells.Clear
.Cells.Font.Size = 9
End With
Str = ThisWorkbook.Path & "" & Sht.Name
Set oFolder = Fso.GetFolder(Str)
Dim Rr, Kk As Integer, Kk1 As Integer
Rr = 10
For Each oFile In oFolder.Files
With Sht
If InStr(oFile.Name, "Ma") > 0 Then
.Cells(Rr + Kk, "A") = oFile.DateLastModified
.Cells(Rr + Kk, "B") = oFile.Name
.Cells(Rr + Kk, "C") = Round(oFile.Size / 1024 ^ 2, 1)
.Cells(Rr + Kk, "Z") = oFile.Path
Kk = Kk + 1
ElseIf InStr(oFile.Name, "Scr") > 0 Then
.Cells(Rr + Kk1, "Aa") = oFile.DateLastModified
.Cells(Rr + Kk1, "AB") = oFile.Name
.Cells(Rr + Kk1, "AC") = oFile.Path
Kk1 = Kk1 + 1
End If
End With
Next oFile
Set Rng = Sht.Cells(Rr, 1).Resize(Kk, 30)
Rng.Select
Rng.Sort Sht.Cells(Rr, 1)
Debug.Print Rng.Address
End Sub

复制代码

		自动登录	找回密码
密码			免费注册