Excel VBA FileSystemObject GetFolder

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")

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")