Sub ListallFiles()
Dim sFileName As String
Dim sFolderPath As String: sFolderPath = "C:\Temp\" ' Change folder path. Ensure that folder path ends with "\"
Dim oWB As Workbook
Dim oWS As Worksheet
' Get the first excel file name from specified folder
sFileName = Dir(sFolderPath & "*.xls*")
' Add a worksheet
Set oWS = ThisWorkbook.Worksheets.Add
With oWS
' Set folder name in the new sheet
.Range("A1").Value = "The file found in " & sFolderPath & " are:"
' Loop through all excel files in the specified folder
Do While Len(Trim(sFileName)) > 0
' Open workbook
Set oWB = Workbooks.Open(sFolderPath & sFileName)
' Set workbook details in the file
.Range("A" & .Cells(.Rows.Count, "A").End(xlUp).Row + 1).Value = sFileName
.Range("B" & .Cells(.Rows.Count, "B").End(xlUp).Row + 1).Value = oWB.Worksheets.Count
' Close workbook
oWB.Close False
' Clear workbook object
Set oWB = Nothing
' Get next excel file
sFileName = Dir()
Loop
End With
End Sub
UDF上面应该打开指定文件夹中的所有文件,并在新工作表上为每个工作簿提供工作表的数量