在VBA中使用DOM创建XML [英] Create XML using DOM in VBA

问题描述

您好在努力使用VBA代码.我正在使用Microsoft XML V6.0 dom对象:

Hi Struggling with VBA code. I am using Microsoft XML V6.0 dom object:

我需要精确创建以下xml:

I need following xml to be created exactly:

<UserAccountDetail Type="Account">
    <AccountName>
        <Name>[e-mail]</Name>
        <Pwd>kdfslj</Pwd>
        <Status>N</Status>
        <ClientId>kdjslj</ClientId>
        <ClientSecret>dkfjsl</ClientSecret>
    </AccountName>
</UserAccountDetail>

到目前为止，这是我的代码:'创建针对xml的处理指令.设置节点= dom.createProcessingInstruction("xml"，"version ='1.0'")dom.appendChild节点设置节点= Nothing

Here is my Code so far: ' Create a processing instruction targeted for xml. Set node = dom.createProcessingInstruction("xml", "version='1.0'") dom.appendChild node Set node = Nothing

' Create a comment for the document.
Set node = dom.createComment("sample xml file created using XML DOM object.")
dom.appendChild node
Set node = Nothing

' Create the root element.

Set root = dom.createElement("AccountName")
' Create a "created" attribute for the root element and
' assign the "using dom" character data as the attribute value.
Set attr = dom.createAttribute("Type")
attr.Value = "Account"
root.setAttributeNode attr
Set attr = Nothing

' Add the root element to the DOM instance.
dom.appendChild root
' Add a newline plus tab.
root.appendChild dom.createTextNode(vbNewLine + vbTab)
' Create a text element Account Name.
Set node = dom.createElement("Name")
node.Text = Trim(UserName)
' Add text node to the root element.
dom.getElementsByTagName("AccountName")(0).appendChild node
Set node = Nothing

' Create a text element Password.
'root(0).appendChild dom.createTextNode(vbNewLine + vbTab)
Set node = dom.createElement("Pwd")
node.Text = Trim(UserPwd)
' Add text node to the root element.
dom.getElementsByTagName("AccountName")(0).appendChild node
node.appendChild dom.createTextNode(vbNewLine + vbTab)
Set node = Nothing

' Create a text element Status.
'root(0).appendChild dom.createTextNode(vbNewLine + vbTab)
Set node = dom.createElement("Status")
node.Text = Trim(Status)
' Add text node to the root element.
dom.getElementsByTagName("AccountName")(0).appendChild node
node.appendChild dom.createTextNode(vbNewLine + vbTab)
Set node = Nothing

' Create a text element Client Id.
'root(0).appendChild dom.createTextNode(vbNewLine + vbTab)
Set node = dom.createElement("ClientId")
node.Text = Trim(strClientId)
' Add text node to the root element.
dom.getElementsByTagName("AccountName")(0).appendChild node
node.appendChild dom.createTextNode(vbNewLine + vbTab)
Set node = Nothing

' Create a text element Client Secret.
'root(0).appendChild dom.createTextNode(vbNewLine + vbTab)
Set node = dom.createElement("ClientSecret")
node.Text = Trim(strClientSecret)
' Add text node to the root element.
dom.getElementsByTagName("AccountName")(0).appendChild node
node.appendChild dom.createTextNode(vbNewLine + vbTab)
Set node = Nothing

推荐答案

使用VBA的MSXML对象创建XML文档不会自动生成带有缩进的漂亮打印输出.要解决此问题，请在创建并处理您的 dom 身份转换 XSLT.>带有子元素的对象.幸运的是，MSXML可以运行XSLT 1.0脚本:

Using VBA's MSXML object to create an XML document will not automatically pretty print output with indentation. To resolve, run an identity transform XSLT just after creating and processing your dom object with child elements. Fortunately, MSXML can run XSLT 1.0 scripts:

Dim xslDoc As New MSXML2.DOMDocument60, newDoc As New MSXML2.DOMDocument60

'...same code to build XML document...'

' PRETTY PRINT RAW OUTPUT '
xslDoc.LoadXML "<?xml version=" & Chr(34) & "1.0" & Chr(34) & "?>" _
        & "<xsl:stylesheet version=" & Chr(34) & "1.0" & Chr(34) _
        & "                xmlns:xsl=" & Chr(34) & "http://www.w3.org/1999/XSL/Transform" & Chr(34) & ">" _
        & "<xsl:strip-space elements=" & Chr(34) & "*" & Chr(34) & " />" _
        & "<xsl:output method=" & Chr(34) & "xml" & Chr(34) & " indent=" & Chr(34) & "yes" & Chr(34) & "" _
        & "            encoding=" & Chr(34) & "UTF-8" & Chr(34) & "/>" _
        & " <xsl:template match=" & Chr(34) & "node() | @*" & Chr(34) & ">" _
        & "  <xsl:copy>" _
        & "   <xsl:apply-templates select=" & Chr(34) & "node() | @*" & Chr(34) & " />" _
        & "  </xsl:copy>" _
        & " </xsl:template>" _
        & "</xsl:stylesheet>"

xslDoc.async = False     
dom.transformNodeToObject xslDoc, newDoc
newDoc.Save ActiveWorkbook.Path & "\Output.xml"

这篇关于在VBA中使用DOM创建XML的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！