vba打开文件夹所有子文件夹里的表格

Sub OpenAllExcelFilesInFolder()
Dim folderPath As String
Dim fileName As String
Dim currentFilePath As String

folderPath = "C:\Users\UserName\Desktop\TestFolder\" 'Replace with your own folder path

'Loop through all files in the folder and subfolders
fileName = Dir(folderPath, vbDirectory)
Do While fileName <> ""
If fileName <> "." And fileName <> ".." Then
currentFilePath = folderPath & fileName
If (GetAttr(currentFilePath) And vbDirectory) = vbDirectory Then 'If this is a folder, call the function recursively
OpenAllExcelFilesInFolder currentFilePath & "\"
ElseIf Right(currentFilePath, 4) = "xlsx" Or Right(currentFilePath, 3) = "xls" Then 'If this is an Excel file, open it
Workbooks.Open currentFilePath
End If
End If
fileName = Dir 'Get the next file name
Loop
End Sub