使用Excel VBA导入XML数据并拆分成多张工作表

Sub ImportAndSplitXMLData()
    Dim xmlDoc As Object
    Dim xmlNodeList As Object
    Dim xmlNode As Object
    Dim ws As Worksheet
    Dim i As Integer
    
    ' 选择要导入的XML文件
    xmlFile = Application.GetOpenFilename("XML Files (*.xml), *.xml")
    
    ' 创建XML对象
    Set xmlDoc = CreateObject("MSXML2.DOMDocument")
    
    ' 加载XML文件
    xmlDoc.Load xmlFile
    
    ' 获取XML根节点
    Set xmlNodeList = xmlDoc.DocumentElement.ChildNodes
    
    ' 创建新的工作表
    Set ws = ThisWorkbook.Sheets.Add
    
    ' 遍历XML节点
    For Each xmlNode In xmlNodeList
        ' 在新的工作表中创建表头
        If i = 0 Then
            ws.Range("A1").Value = "Column 1"
            ws.Range("B1").Value = "Column 2"
            ' ...
        End If
        
        ' 在新的工作表中填充数据
        ws.Range("A" & i + 2).Value = xmlNode.SelectSingleNode("Column1").Text
        ws.Range("B" & i + 2).Value = xmlNode.SelectSingleNode("Column2").Text
        ' ...
        
        i = i + 1
        
        ' 每个工作表最多存储1000行数据，超过则创建新的工作表
        If i Mod 1000 = 0 Then
            Set ws = ThisWorkbook.Sheets.Add
            i = 0
        End If
    Next xmlNode
    
    ' 清理对象
    Set xmlDoc = Nothing
    Set xmlNodeList = Nothing
    Set xmlNode = Nothing
    Set ws = Nothing
    
    MsgBox "XML数据导入并拆分完成！"
End Sub