如何使用Excel VBA从子文件夹加载所有XML文件？

Sub LoadXMLFilesFromSubfolders()
    Dim FolderPath As String
    Dim FileName As String
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim XMLFile As Object
    
    ' 设置文件夹路径
    FolderPath = "C:\YourFolderPath\"
    
    ' 创建新工作簿
    Set wb = Workbooks.Add
    
    ' 循环遍历子文件夹
    FileName = Dir(FolderPath & "*", vbDirectory)
    Do While FileName <> ""
        ' 排除当前文件夹和上级文件夹
        If FileName <> "." And FileName <> ".." Then
            ' 检查文件类型是否为XML
            If Right(FileName, 4) = ".xml" Then
                ' 打开XML文件
                Set XMLFile = CreateObject("MSXML2.DOMDocument")
                XMLFile.Load (FolderPath & FileName)
                
                ' 在新工作簿中创建新工作表
                Set ws = wb.Sheets.Add(After:=wb.Sheets(wb.Sheets.Count))
                ws.Name = FileName
                
                ' 将XML数据导入新工作表
                XMLFile.ChildNodes(0).CopyNode ws.Range("A1")
                
                ' 关闭XML文件
                Set XMLFile = Nothing
            End If
        End If
        
        ' 获取下一个文件名
        FileName = Dir
    Loop
End Sub