Questions about exporting an xml using MSXML2

MPW

Well-known Member
Joined
Oct 7, 2009
Messages
571
Office Version
  1. 365
Platform
  1. Windows
howdy,
My goal is to create and export 8 columns of data from Excel into a xml formatted file. I have never tried to export to xml and so I am not familiar with this.
I have been experimenting with code that I found at: (How to generate an XML document from within VBA - VBA Visual Basic for Applications (Microsoft) FAQ - Tek-Tips)

I got stuck on creating new lines and indenting. Here is where I stopped.
VBA Code:
    Set xmlVersion = xmlDom.createProcessingInstruction("xml", "version=" & Chr(QUOTE_MARK) & "1.0" & Chr(QUOTE_MARK) & " encoding=" & Chr(QUOTE_MARK) & "UTF-8" & Chr(QUOTE_MARK))
    xmlDom.appendChild xmlVersion
    Set xmlRootNode = xmlDom.createElement("RootNode")
    xmlDom.appendChild xmlRootNode
    xmlDom.DocumentElement.setAttribute XML_NAMESPACE_ATTR, XML_NAMESPACE_VALUE
    Set xmlElement = xmlDom.createElement("language")
    xmlRootNode.appendChild xmlElement
    xmlElement.Text = lgn
This produces:
SQL:
<?xml version="1.0" encoding="UTF-8"?>
<RootNode xmlns:wow="http://www.example.com/ns/1.0/"><language>Subanon Western</language></RootNode>
What I am trying to achieve is to emulate this:
SQL:
<?xml version="1.0" encoding="utf-8"?>
<glyssenScript id="aa810b0bf8dfb100" revision="2" modifieddate="2021-03-09T11:01:44.7532417-07:00" uniqueProjectId="b4e8b777-9476-413d-b291-0a816d5a8e68" audiostocknumber="N2SUCWBT" version="2.2" projectName="Western Subanon Version Audio">
  <language>
    <iso>suc</iso>
    <name>Western Subanon</name>
    <ldml>suc</ldml>
    <fontFamily>Verdana</fontFamily>
    <fontSizeInPoints>12</fontSizeInPoints>
    <fontSizeUiAdjustment>1</fontSizeUiAdjustment>

I desire to make the <language> portion to be on a new line and indented to reflect that it is a child of the <RootNode>. I found that vbtab and vbnewline do work if I can get them in the correct position.

Could somebody break down the mechanics on the (appendChild) element and what other levels are available? I have found very few examples of how to use MSXML2 in this manner. I still need to add 10,000 new rows, but if I can understand the logic, I should be able to work the rest out.

Thanks for being there!
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top