Jump to content

List of list in XML using VBA?


pcrist
 Share

Recommended Posts

I need to create a list inside of a list in an xml file such that it looks like this: <?xml version="1.0" encoding="UTF-8" ?><InUseVerificationProgramSubmission xmlns="http://www.exchangen...a/verify/iuvp/1">

<SubmissionAuthorDetails>
<EPAManufacturerCode>
MBX
</EPAManufacturerCode>
<SubmissionAuthorUserID>
CETNAR712
</SubmissionAuthorUserID>
<SubmissionAuthorFullName>
Mr Charles Cetnar
</SubmissionAuthorFullName>
<SubmissionAuthorEmailAddress>
charles.cetnar@daimler.com
</SubmissionAuthorEmailAddress>
<SubmissionAuthorPhoneNumber>
7349953066
</SubmissionAuthorPhoneNumber>
<SubmissionSubmitterUserID>
CETNAR712
</SubmissionSubmitterUserID>
<SubmissionParameterDetails>
<SubmissionParameterKey>
CARBFileName
</SubmissionParameterKey>
<SubmissionParameterText>
MBX_IUVP_20121108093549.xml
</SubmissionParameterText>
</SubmissionParameterDetails>
</SubmissionAuthorDetails>
Here is the code I'm using.
Private Function CreateDOM()	Dim dom	Set dom = New DOMDocument60	dom.async = False	dom.validateOnParse = False	dom.resolveExternals = False	dom.preserveWhiteSpace = True	Set CreateDOM = domEnd FunctionPrivate Sub GetDataCB_Click()End SubPrivate 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	Set indent = 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")		indent.appendChild dom.createTextNode(vbNewLine + vbTab + vbTab + vbTab)		indent.appendChild dom.createElement("SubmissionParameterDetails")	frag.appendChild indent	Set indent = Nothing	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 SubEnd Sub

Link to comment
Share on other sites

I just solved my problem with this changed set of code:

    ' 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")	    frag.appendChild dom.createTextNode(vbNewLine + vbTab + vbTab)	       Set indent = dom.createElement("SPD")    Set frag_node = dom.createDocumentFragment	    frag_node.appendChild dom.createTextNode(vbNewLine + vbTab + vbTab + vbTab)	    frag_node.appendChild dom.createElement("SPK")	       indent.appendChild frag_node    frag.appendChild indent    node.appendChild frag    root.appendChild node

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...