在Excel中使用VBA文件夹中的文件列表

Option Explicit

Sub subfolder_files(folderpath1 As Variant, Optional include_subfolder As Boolean)

'Checking whether to include subfolder or not

If include_subfolder Then



'Declaring variables

Dim filename, filearray() As String

Dim lastrow, count1, i As Integer



'Checking whether folder path contain backslash as last character

If Right(folderpath1, 1) <> "\" Then

folderpath1 = folderpath1 & "\"

End If



'Getting the filename of the first file in the defined folder path

filename = Dir(folderpath1 & "*.xlsx")



'Getting the row number of last cell

lastrow = ActiveCell.SpecialCells(xlCellTypeLastCell).Row + 1



count1 = 0



'Looping through all the files in the folder

While filename <> ""

count1 = count1 + 1

ReDim Preserve filearray(1 To count1)

filearray(count1) = filename

filename = Dir()

Wend



On Error GoTo last



'Adding file name to workbook

For i = 1 To UBound(filearray)

Cells(lastrow, 1).Value = folderpath1 & filearray(i)

lastrow = lastrow + 1

Next



End If

last:

End Sub

Sub getting_filelist_in_folder()

'Declaring variables

Dim folder_path As String

Dim fso As Object, folder1, subfolder1 As Object

'Getting path of the folder

folder_path = Sheet1.TextBox1.Value

'Checking whether folder path contain backslash as last character

If Right(folder_path, 1) <> "\" Then

folder_path = folder_path & "\"

End If

'Calling subfolder_files macro

Call subfolder_files(folder_path, True)

'Creating object of File system object

Set fso = CreateObject("scripting.filesystemobject")

Set subfolder1 = fso.getfolder(folder_path)

'Looping through each subfolder

For Each folder1 In subfolder1.subfolders

Call subfolder_files(folder1, True)

Next

End Sub