none
List of list in VBA to create XML RRS feed

  • Question

  • I need to create a list inside of a list in an xml file such that it looks like this:

    \So SubmissionParameterDetails has childNodes but is also a childNode itself of SubmissionAuthorDetails. How do I accomplish this?

    Here is the code I'm using without any luck.

    Private Function CreateDOM()
        Dim dom
        Set dom = New DOMDocument60
        dom.async = False
        dom.validateOnParse = False
        dom.resolveExternals = False
        dom.preserveWhiteSpace = True
        Set CreateDOM = dom
    End Function
    Private Sub GetDataCB_Click()
    End Sub
    Private Sub ExportXMLCB_Click()
        Dim dom, node
        
        Set dom = CreateDOM
        
        ' Create a processing instruction targeted for xml.
        Set node = dom.createProcessingInstruction("xml", "version='1.0'")
        dom.appendChild node
        Set node = Nothing
        
        ' Create a processing instruction targeted for xml-stylesheet.
        Set node = dom.createProcessingInstruction("xml-VERIFY", _
                                    "type='text/xml'")
        dom.appendChild node
        Set node = Nothing
        
        ' Create a comment for the document.
        Set node = dom.createComment("XML File Creation for VERIFY data submission.")
        dom.appendChild node
        Set node = Nothing
        
        ' Create the root element
        Dim root
        Set root = dom.createElement("InUseVerificationProgramSubmission")
        dom.appendChild root
        
        ' Create SubmissionAuthorDetails element'
        root.appendChild dom.createTextNode(vbNewLine + vbTab)
        Set node = dom.createElement("SubmissionAuthorDetails")
        Set frag = dom.createDocumentFragment
            frag.appendChild dom.createTextNode(vbNewLine + vbTab + vbTab)
            frag.appendChild dom.createElement("EPAManufacturerCode")
            frag.appendChild dom.createTextNode(vbNewLine + vbTab + vbTab)
            frag.appendChild dom.createElement("SubmissionAuthorUserID")
            frag.appendChild dom.createTextNode(vbNewLine + vbTab + vbTab)
            frag.appendChild dom.createElement("SubmissionAuthorFullName")
            frag.appendChild dom.createTextNode(vbNewLine + vbTab + vbTab)
            frag.appendChild dom.createElement("SubmissionAuthorEmailAddress")
            frag.appendChild dom.createTextNode(vbNewLine + vbTab + vbTab)
            frag.appendChild dom.createElement("SubmissionAuthorPhoneNumber")
            frag.appendChild dom.createTextNode(vbNewLine + vbTab + vbTab)
            frag.appendChild dom.createElement("SubmissionSubmitterUserID")
        node.appendChild frag
        Set frag = Nothing
        root.appendChild node
        
        
        ' Save the XML document to a file.
        dom.Save (ThisWorkbook.Path & "\test.xml")
        Set root = Nothing
        Set dom = Nothing
        MsgBox ("XML export complete.")
    Exit Sub
    End Sub



    • Moved by Mark Liu-lxfModerator Tuesday, November 27, 2012 4:12 AM (From:Visual Basic IDE)
    • Edited by Peterc13 Tuesday, November 27, 2012 3:34 PM erase info
    Monday, November 26, 2012 4:58 PM

Answers

  • Try Below.Hope it helps...
    Private Sub ExportXMLCB_Click()
        
        Dim dom As DOMDocument50
        Dim node As IXMLDOMNode
        Dim cnode As IXMLDOMNode
        Dim dnode As IXMLDOMNode
        Dim root As IXMLDOMNode
        Dim att As IXMLDOMNode
            
        Set dom = CreateDOM
        
        ' Create a processing instruction targeted for xml.
        dom.appendChild dom.createProcessingInstruction("xml", _
            "version='1.0' encoding='UTF-8'")
        
        ' Create the root element and add attribute
        Set root = dom.createElement("InUseVerificationProgramSubmission")
        Set att = dom.createAttribute("xmlns")
            att.NodeValue = "'http://www.exchangen...a/verify/iuvp/1'"
            root.Attributes.setNamedItem att
        dom.appendChild root
        
            'Adding 1st child of root
            Set cnode = dom.createElement("SubmissionAuthorDetails")
            root.appendChild cnode
        
                'Adding grand childs
                
                'EPAManufacturerCode
                Set node = dom.createElement("EPAManufacturerCode")
                node.appendChild dom.createTextNode("MBX")
                cnode.appendChild node
                
                'SubmissionAuthorUserID
                Set node = dom.createElement("SubmissionAuthorUserID")
                node.appendChild dom.createTextNode("CETNAR712")
                cnode.appendChild node
                
                'SubmissionAuthorUserID
                Set node = dom.createElement("SubmissionAuthorUserID")
                node.appendChild dom.createTextNode("CETNAR712")
                cnode.appendChild node
                
                'SubmissionAuthorFullName
                Set node = dom.createElement("SubmissionAuthorFullName")
                node.appendChild dom.createTextNode("Mr Charles Cetnar")
                cnode.appendChild node
                
                'SubmissionAuthorEmailAddress
                Set node = dom.createElement("SubmissionAuthorEmailAddress")
                node.appendChild dom.createTextNode("charles.cetnar@daimler.com")
                cnode.appendChild node
                
                'SubmissionAuthorPhoneNumber
                Set node = dom.createElement("SubmissionAuthorPhoneNumber")
                node.appendChild dom.createTextNode("charles.cetnar@daimler.com")
                cnode.appendChild node
                
                'SubmissionSubmitterUserID
                Set node = dom.createElement("SubmissionSubmitterUserID")
                node.appendChild dom.createTextNode("CETNAR712")
                cnode.appendChild node
                
                'SubmissionParameterDetails
                
                    'Adding 1st child (dnode) to ParameterDetails(node)
                    Set node = dom.createElement("SubmissionParameterDetails")
                    Set dnode = dom.createElement("SubmissionParameterKey")
                    dnode.appendChild dom.createTextNode("CARBFileName")
                
                    node.appendChild dnode
                
                    'Adding 2nd child (dnode) to ParameterDetails(node)
                    Set dnode = dom.createElement("SubmissionParameterText")
                    dnode.appendChild dom.createTextNode("MBX_IUVP_20121108093549.xml")
                    node.appendChild dnode
                    
                    'Adding Parameter details to cnode
                    cnode.appendChild node
        
        ' Save the XML document to a file.
        dom.Save (ThisWorkbook.Path & "\test.xml")
        
        'Housekeeping
        Set root = Nothing
        Set dom = Nothing
        MsgBox ("XML export complete.")
    
    End Sub


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    • Marked as answer by Peterc13 Tuesday, November 27, 2012 3:02 PM
    Tuesday, November 27, 2012 8:24 AM
    Answerer

All replies

  • Hi Peterc,

    Welcome to the MSDN forum.

    This forum is for VB.net, not VBA. Microsoft provides a special forum to deal with VBA issue. For better support, I will move this thread to VBA forum. It will cost a little time to involve the members in this forum. I appreciate your patience.

    Sorry for any incontinence and have a nice day.


    Mark Liu-lxf [MSFT]
    MSDN Community Support | Feedback to us

    Tuesday, November 27, 2012 4:11 AM
    Moderator
  • Try Below.Hope it helps...
    Private Sub ExportXMLCB_Click()
        
        Dim dom As DOMDocument50
        Dim node As IXMLDOMNode
        Dim cnode As IXMLDOMNode
        Dim dnode As IXMLDOMNode
        Dim root As IXMLDOMNode
        Dim att As IXMLDOMNode
            
        Set dom = CreateDOM
        
        ' Create a processing instruction targeted for xml.
        dom.appendChild dom.createProcessingInstruction("xml", _
            "version='1.0' encoding='UTF-8'")
        
        ' Create the root element and add attribute
        Set root = dom.createElement("InUseVerificationProgramSubmission")
        Set att = dom.createAttribute("xmlns")
            att.NodeValue = "'http://www.exchangen...a/verify/iuvp/1'"
            root.Attributes.setNamedItem att
        dom.appendChild root
        
            'Adding 1st child of root
            Set cnode = dom.createElement("SubmissionAuthorDetails")
            root.appendChild cnode
        
                'Adding grand childs
                
                'EPAManufacturerCode
                Set node = dom.createElement("EPAManufacturerCode")
                node.appendChild dom.createTextNode("MBX")
                cnode.appendChild node
                
                'SubmissionAuthorUserID
                Set node = dom.createElement("SubmissionAuthorUserID")
                node.appendChild dom.createTextNode("CETNAR712")
                cnode.appendChild node
                
                'SubmissionAuthorUserID
                Set node = dom.createElement("SubmissionAuthorUserID")
                node.appendChild dom.createTextNode("CETNAR712")
                cnode.appendChild node
                
                'SubmissionAuthorFullName
                Set node = dom.createElement("SubmissionAuthorFullName")
                node.appendChild dom.createTextNode("Mr Charles Cetnar")
                cnode.appendChild node
                
                'SubmissionAuthorEmailAddress
                Set node = dom.createElement("SubmissionAuthorEmailAddress")
                node.appendChild dom.createTextNode("charles.cetnar@daimler.com")
                cnode.appendChild node
                
                'SubmissionAuthorPhoneNumber
                Set node = dom.createElement("SubmissionAuthorPhoneNumber")
                node.appendChild dom.createTextNode("charles.cetnar@daimler.com")
                cnode.appendChild node
                
                'SubmissionSubmitterUserID
                Set node = dom.createElement("SubmissionSubmitterUserID")
                node.appendChild dom.createTextNode("CETNAR712")
                cnode.appendChild node
                
                'SubmissionParameterDetails
                
                    'Adding 1st child (dnode) to ParameterDetails(node)
                    Set node = dom.createElement("SubmissionParameterDetails")
                    Set dnode = dom.createElement("SubmissionParameterKey")
                    dnode.appendChild dom.createTextNode("CARBFileName")
                
                    node.appendChild dnode
                
                    'Adding 2nd child (dnode) to ParameterDetails(node)
                    Set dnode = dom.createElement("SubmissionParameterText")
                    dnode.appendChild dom.createTextNode("MBX_IUVP_20121108093549.xml")
                    node.appendChild dnode
                    
                    'Adding Parameter details to cnode
                    cnode.appendChild node
        
        ' Save the XML document to a file.
        dom.Save (ThisWorkbook.Path & "\test.xml")
        
        'Housekeeping
        Set root = Nothing
        Set dom = Nothing
        MsgBox ("XML export complete.")
    
    End Sub


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    • Marked as answer by Peterc13 Tuesday, November 27, 2012 3:02 PM
    Tuesday, November 27, 2012 8:24 AM
    Answerer
  • Thank you. I did something very similar and it makes sense now. Thanks again!!!
    Tuesday, November 27, 2012 2:54 PM
  • One quick question:

    I need the first few lines to look like this:

      <?xml version="1.0" encoding="UTF-8" ?> 
    - <InUseVerificationProgramSubmission xmlns="http://www.exchangenetwork.net/schema/verify/iuvp/1">
    - <SubmissionAuthorDetails>
      <EPAManufacturerCode>MBX</EPAManufacturerCode> 

    However, the code I followed from the above example causes 'xmlns=' to appear after every cnode (i.e. SubmissionAuthorDetails). It looks like this:

      <?xml version="1.0" encoding="UTF-8" ?> 
    - <InUseVerificationProgramSubmission xmlns="'http://www.exchangenetwork.net/schema/verify/iuvp/1'">
    - <SubmissionAuthorDetails xmlns="">
      <EPAManufacturerCode>MBX</EPAManufacturerCode>
    How do I make sure the 'xmlns' tag only appears for the "InUseVerificationProgramSubmission" element and not the child nodes?

    Wednesday, November 28, 2012 3:04 PM