excel - Excel VBA FileSystemObject 获取文件夹

Question

我希望能够获取有关我输入一系列单元格的路径列表的文件信息。我也不想得到所有的子文件夹。我的这段代码使用 1 个文件夹路径效果很好。

Sub Get_Information()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Sheet1")

Dim fso As New FileSystemObject
Dim fo As Folder
Dim f As File

Dim last_row As Integer

sh.Rows(1).Font.Size = 18

Set fo = fso.GetFolder(sh.Range("H1").Value)

For Each f In fo.Files
    last_row = sh.Range("A" & Application.Rows.Count).End(xlUp).Row + 1

    sh.Range("A" & last_row).Value = f.Name
    sh.Range("B" & last_row).Value = f.Type
    sh.Range("C" & last_row).Value = f.Size / 1024
    sh.Range("D" & last_row).Value = f.DateLastModified

Next

MsgBox ("Done")

标签： excelvbafilesystemobject

Answer 1

如果您在一个单元格中有所有路径，则可以在该单元格中拆分字符串，然后使用数组循环

Sub Get_Information()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Sheet1")

Dim fso As New FileSystemObject
Dim fo As Folder
Dim f As File
Dim pathArray as Variant
Dim SplitString as String    

Dim last_row As Integer

sh.Rows(1).Font.Size = 18

SplitString = sh.Range("H1").Value
pathArray = Split(SplitString, ";") 'change to whatever seperator you are using

For each pth in pathArray

    Set fo = fso.GetFolder(pth)

    For Each f In fo.Files
        last_row = sh.Range("A" & Application.Rows.Count).End(xlUp).Row + 1

        sh.Range("A" & last_row).Value = f.Name
        sh.Range("B" & last_row).Value = f.Type
        sh.Range("C" & last_row).Value = f.Size / 1024
        sh.Range("D" & last_row).Value = f.DateLastModified

    Next f

Next pth
MsgBox ("Done")

编辑

如果您想循环遍历一系列单元格：

Sub Get_Information()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Sheet1")

Dim fso As New FileSystemObject
Dim fo As Folder
Dim f As File
Dim c as Range

Dim last_row As Integer

sh.Rows(1).Font.Size = 18

For each pth in sh.Range("H1:H" & last_row) 'Edit range
  If not pth.value = ""
    Set fo = fso.GetFolder(c.Value)

    For Each f In fo.Files
        last_row = sh.Range("A" & Application.Rows.Count).End(xlUp).Row + 1

        sh.Range("A" & last_row).Value = f.Name
        sh.Range("B" & last_row).Value = f.Type
        sh.Range("C" & last_row).Value = f.Size / 1024
        sh.Range("D" & last_row).Value = f.DateLastModified

    Next f

  End If
Next pth
MsgBox ("Done")