vba - Pulling nested data from XML to excel - Stack Overflow

admin管理员组

文章数量:1277910

I am trying to parse an xml and need to pull specific data to excel format. Below is the xml i am trying to parse. I am trying to pull the Status and DR.

For some reason, my code is throwing this error message:Run time error 91: Object variable or with block variable not set.

It highlights the code:

Sheets("Sheet1").Cells(i, 4).Value = mlfda.getElementsByTagName("Status")(0).Text

see my entire code below:

Sub PullComplexXML()

    Dim xmlDoc As Object
    Dim XMLPGA As Object
    Dim xmlfda As Object
    Dim i As Integer        
        
    'Dim xmlDoc As Object
    Set xmlDoc = CreateObject("MSXML2.DOMDocument")
    xmlDoc.Load ("C:\books.xml")

    ' Adjust "SectionName"
    Dim sectionNode As Object
    Set sectionNode = xmlDoc.SelectSingleNode("//PGAs")

    If Not sectionNode Is Nothing Then
        Dim childNode As Object
        For Each childNode In sectionNode.ChildNodes
            Debug.Print childNode.Text
        Next childNode
    Else
        MsgBox "Section not found"
    End If

    'Pull nested data

     ' Create a new XML Document object
     Set xmlDoc = CreateObject("MSXML2.DOMDocument.6.0")

        ' Configure properties
        xmlDoc.async = False
        xmlDoc.validateOnParse = False

        ' Load the XML file
        If Not xmlDoc.Load("C:\books.xml") Then
            MsgBox "Failed to load XML file. Exiting."
            Exit Sub
        End If

        i = 2
        'Loop through each PGA
         For Each XMLPGA In xmlDoc.DocumentElement.ChildNodes
            
            'Loop through each FDA            
             For Each xmlfda In XMLPGA.ChildNodes
                Sheets("Sheet1").Cells(i, 1).Value = xmlDoc.DocumentElement.getAttribute("Product") 'Product
                Sheets("Sheet1").Cells(i, 2).Value = XMLPGA.getAttribute("PGAs") ' PGA
                Sheets("Sheet1").Cells(i, 3).Value = xmlfda.getAttribute("FDA") ' FDA
                
                Sheets("Sheet1").Cells(i, 4).Value = xmlfda.getElementsByTagName("Status")(0).Text '  Status
                Sheets("Sheet1").Cells(i, 5).Value = xmlfda.getElementsByTagName("DisclaimReason")(0).Text ' DR
                            
                i = i + 1
            Next xmlfda
        Next XMLPGA

        Set xmlDoc = Nothing
        MsgBox "Data loaded. Now validate your file!"
End Sub

I am trying to parse an xml and need to pull specific data to excel format. Below is the xml i am trying to parse. I am trying to pull the Status and DR.

For some reason, my code is throwing this error message:Run time error 91: Object variable or with block variable not set.

It highlights the code:

Sheets("Sheet1").Cells(i, 4).Value = mlfda.getElementsByTagName("Status")(0).Text

see my entire code below:

Sub PullComplexXML()

    Dim xmlDoc As Object
    Dim XMLPGA As Object
    Dim xmlfda As Object
    Dim i As Integer        
        
    'Dim xmlDoc As Object
    Set xmlDoc = CreateObject("MSXML2.DOMDocument")
    xmlDoc.Load ("C:\books.xml")

    ' Adjust "SectionName"
    Dim sectionNode As Object
    Set sectionNode = xmlDoc.SelectSingleNode("//PGAs")

    If Not sectionNode Is Nothing Then
        Dim childNode As Object
        For Each childNode In sectionNode.ChildNodes
            Debug.Print childNode.Text
        Next childNode
    Else
        MsgBox "Section not found"
    End If

    'Pull nested data

     ' Create a new XML Document object
     Set xmlDoc = CreateObject("MSXML2.DOMDocument.6.0")

        ' Configure properties
        xmlDoc.async = False
        xmlDoc.validateOnParse = False

        ' Load the XML file
        If Not xmlDoc.Load("C:\books.xml") Then
            MsgBox "Failed to load XML file. Exiting."
            Exit Sub
        End If

        i = 2
        'Loop through each PGA
         For Each XMLPGA In xmlDoc.DocumentElement.ChildNodes
            
            'Loop through each FDA            
             For Each xmlfda In XMLPGA.ChildNodes
                Sheets("Sheet1").Cells(i, 1).Value = xmlDoc.DocumentElement.getAttribute("Product") 'Product
                Sheets("Sheet1").Cells(i, 2).Value = XMLPGA.getAttribute("PGAs") ' PGA
                Sheets("Sheet1").Cells(i, 3).Value = xmlfda.getAttribute("FDA") ' FDA
                
                Sheets("Sheet1").Cells(i, 4).Value = xmlfda.getElementsByTagName("Status")(0).Text '  Status
                Sheets("Sheet1").Cells(i, 5).Value = xmlfda.getElementsByTagName("DisclaimReason")(0).Text ' DR
                            
                i = i + 1
            Next xmlfda
        Next XMLPGA

        Set xmlDoc = Nothing
        MsgBox "Data loaded. Now validate your file!"
End Sub

• excel
• vba
• xml

Share Improve this question Follow edited Feb 24 at 18:39 Tim Williams 167k88 gold badges100100 silver badges139139 bronze badges asked Feb 24 at 3:27 jackiejackie 3522 silver badges77 bronze badges 1

• Why do you have array index zero? Maybe try left instead. – jdweng Commented Feb 24 at 3:34

Add a comment  | 

3 Answers 3

Sorted by: Reset to default 0

You need a closing tag </PGAs> for <PGAs>.

I can also suggest that you use FILTERXML function; which can do this. If your XML is in cell A1, then =FILTERXML(A1,"//Status") would give you D

Option Explicit

Sub PullComplexXML()

    Dim xmlDoc As Object, nodes, node1, node2
    Dim sXML As String
    sXML = "<SKU><PGAs>" & _
       "<FDA><UI>1</UI><Description /><Note /><Status>D1</Status><DR>A1</DR></FDA>" & _
       "<FDA><UI>2</UI><Description /><Note /><Status>D2</Status><DR>A2</DR></FDA>" & _
       "<FDA><UI>3</UI><Description /><Note /><Status>D3</Status><DR>A3</DR></FDA>" & _
    "</PGAs></SKU>"
    
    Set xmlDoc = CreateObject("MSXML2.DOMDocument")
    With xmlDoc
        .async = False
        .validateOnParse = True
         '.Load "C:\books.xml"
         .LoadXML sXML
         Set nodes = .SelectNodes("/SKU/PGAs/*")
    End With
    
    ' dictionary of tag names to column
    Dim dict As Object, k, bInc As Boolean
    Dim r As Long, c As Long, sTag As String
    Set dict = CreateObject("Scripting.Dictionary")
    dict.Add "UI", 1
    dict.Add "Status", 2
    dict.Add "DR", 3
    
    ' header
    For Each k In dict
        Sheet1.Cells(1, dict(k)) = k
    Next
    
    'Loop through each node
    r = 2
    For Each node1 In nodes
        bInc = False
        For Each node2 In node1.ChildNodes
            sTag = node2.nodename
            'Debug.Print sTag
            If dict.exists(sTag) Then
                c = dict(sTag) ' column
                Sheet1.Cells(r, c) = node2.Text
                bInc = True
            End If
        Next
        If bInc Then r = r + 1
    Next
    
    'result
    MsgBox r - 2 & " rows loaded. Now validate your file!", vbInformation
End Sub

I won’t be providing assistance with your VBA problem because I believe you are using the wrong tool for this task. There is no need to write custom VBA code for every possible XML structure when Excel already provides built-in tools designed for handling such scenarios efficiently.

Power Query allows you to load and transform XML files without the need for complex, error-prone code. It automatically detects hierarchical structures, enabling you to extract and manipulate the data with minimal effort.

I strongly recommend exploring Power Query instead of VBA.

本文标签： vbaPulling nested data from XML to excelStack Overflow