Jump to content
Sign in to follow this  
pcrist

List of list in XML using VBA?

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

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...