Excel VBA -从字符串中插入XML

I'm trying to write a VBS script that will take an XML string and insert it into Excel using a XSL stylesheet.

我正在尝试编写一个VBS脚本，它将使用一个XML字符串，并使用XSL样式表将其插入到Excel中。

I did something similar recently in Word, and the InsertXML function took the XSL file as one of the parameters. It doesn't seem to be quite so simple in Excel, though.

我最近在Word中做了一些类似的事情，InsertXML函数将XSL文件作为参数之一。不过，在Excel中似乎并没有这么简单。

I'm a bit flummoxed by the whole concept of maps and schemas.

我被地图和图式的整个概念搞糊涂了。

The XmlImportXml function will take a string, but it also needs a map.

XmlImportXml函数将使用一个字符串，但它也需要一个映射。

I tried Maps.Add(strXML), but then there's no option to apply the stylesheet.

我尝试了map . add (strXML)，但是没有选择应用样式表。

I've tried pre-translating my XML by using the transformNode function of the XMLDOM, but then the Maps.Add is completely confused by my layout. (If I export the transformed XML to a file, and then open that XML in Excel, it's exactly what I want).

我已经尝试过使用XMLDOM的transformNode函数来预译我的XML，但之后又使用了映射。Add完全被我的布局搞糊涂了。(如果我将转换后的XML导出到一个文件，然后在Excel中打开那个XML，这正是我想要的)。

Creating a schema seems to require XMLTools? I don't have administrative rights to my box to install it.

创建一个模式似乎需要XMLTools?我没有权限安装我的盒子。

I've even tried saving the XML file and defining the stylesheet upfront, but when I open it, Excel still says I don't have a defined schema.

我甚至尝试过保存XML文件并预先定义样式表，但是当我打开它时，Excel仍然说我没有定义的模式。

There are LOTS of resources on creating Excel XLS stylesheets, so I feel like I must be missing something very simple about how to USE them.

有很多关于创建Excel XLS样式表的资源，所以我觉得我一定遗漏了一些关于如何使用它们的非常简单的东西。

In case you need it, here is some sample XML:

如果您需要它，这里有一些示例XML:

<?xml version="1.0" standalone="yes" ?>
<?xml-stylesheet type="text/xsl" href="Snapshot_Excel.xsl"?>
<RpcData SrcNm="SnapshotBuckets" SrcTyp="DIR" ClientID="000" LoanNo="0000000000" Borrower="" RsltCd="0">
  <RepeatingFieldSet Nm="Hazard" Type="All Data" Count="3">
    <Row Index="1">
      <Fld Nm="Type">A</Fld>
      <Fld Nm="AgentCode">TESTAP</Fld>
      <Fld Nm="Agent City">ANYTOWN</Fld>
      <Fld Nm="Agent Desc Line 1">APPLE</Fld>
      <Fld Nm="Agent Desc Line 2">PICKERS</Fld>
      <Fld Nm="Agent Desc Line 3">123 MAIN ST</Fld>
      <Fld Nm="Agent Phone">[phone]</Fld>
      <Fld Nm="Agent State">AL</Fld>
      <Fld Nm="Agent ZIP Code">00001</Fld>
    </Row>
    <Row Index="2">
      <Fld Nm="Type">B</Fld>
      <Fld Nm="AgentCode">TESTBA</Fld>
      <Fld Nm="Agent City">ANYTOWN</Fld>
      <Fld Nm="Agent Desc Line 1">BANANA</Fld>
      <Fld Nm="Agent Desc Line 2">BUNCHERS</Fld>
      <Fld Nm="Agent Desc Line 3">456 MAIN ST</Fld>
      <Fld Nm="Agent Phone">[phone]</Fld>
      <Fld Nm="Agent State">AK</Fld>
      <Fld Nm="Agent ZIP Code">00002</Fld>
    </Row>
    <Row Index="3">
      <Fld Nm="Type">C</Fld>
      <Fld Nm="AgentCode">TESTCH</Fld>
      <Fld Nm="Agent City">ANYTOWN</Fld>
      <Fld Nm="Agent Desc Line 1">CHERRY</Fld>
      <Fld Nm="Agent Desc Line 2">PITTERS</Fld>
      <Fld Nm="Agent Desc Line 3">789 MAIN ST</Fld>
      <Fld Nm="Agent Phone">[phone]</Fld>
      <Fld Nm="Agent State">CA</Fld>
      <Fld Nm="Agent ZIP Code">00003</Fld>
    </Row>
  </RepeatingFieldSet>
</RpcData>

And my XSL file:

和我的XSL文件:

<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:x="urn:schemas-microsoft-com:office:excel">
    <xsl:template match="/RpcData">
        <xsl:variable name="col" select="RepeatingFieldSet/Row"/>
        <xsl:variable name="row" select="RepeatingFieldSet/Row[1]/Fld"/>
        <Workbook>
            <Worksheet ss:Name="Sheet1">
                <Table>
                    <!-- header row -->
                    <Row>
                        <Cell>
                            <Data ss:Type="String">Field</Data>
                        </Cell>
                        <xsl:for-each select="$col">
                            <Cell>
                                <Data ss:Type="String"><xsl:value-of select="@Index"/></Data>
                            </Cell>
                         </xsl:for-each>
                    </Row>
                    <!-- data rows -->
                    <xsl:for-each select="$row">
                        <xsl:variable name="i" select="position()"/>
                        <Row>
                            <Cell>
                                <Data ss:Type="String"><xsl:value-of select="@Nm"/></Data>
                            </Cell>
                            <xsl:for-each select="$col">
                                <Cell>
                                    <Data ss:Type="String"><xsl:value-of select="Fld[$i]"/></Data>
                                </Cell>
                            </xsl:for-each>
                        </Row>    
                    </xsl:for-each>
                </Table>
            </Worksheet>
        </Workbook>
    </xsl:template> 
</xsl:stylesheet>

Thanks in advance!

提前谢谢!

1 个解决方案

#1

Use the Workbooks.OpenXML method and specify the 1 to indicate the XSLT stylesheet in your first processing instruction: <?xml-stylesheet type="text/xsl" href="Snapshot_Excel.xsl"?>:

使用工作簿。OpenXML方法，并在第一个处理指令 :

Workbooks.OpenXML "C:\Path\To\XML\File.xml", 1, xlXmlLoadImportToList

Alternatively, you can directly process the XSLT with VBA's MSXML object to transform the source XML and then load into a workbook:

或者，您可以使用VBA的MSXML对象直接处理XSLT，以转换源XML，然后将其加载到工作簿中:

Public Sub RunXSLT()
    Dim xmlDoc As Object, xslDoc As Object, newDoc As Object

    Set xmlDoc = CreateObject("MSXML2.DOMDocument")
    Set xslDoc = CreateObject("MSXML2.DOMDocument")
    Set newDoc = CreateObject("MSXML2.DOMDocument")

    ' LOAD XML AND XSL '  
    xmlDoc.Load "C:\Path\To\Input.xml"
    xmlDoc.async = False

    xslDoc.Load "C:\Path\To\Script.xsl"
    xslDoc.async = False

    ' TRANSFORM AND SAVE OUTPUT '
    xmlDoc.transformNodeToObject xslDoc, newDoc
    newDoc.Save "C:\Path\To\Output.xml"

    Set newDoc = Nothing
    Set xslDoc = Nothing
    Set xmlDoc = Nothing

    ' LOAD OUTPUT INTO WORKBOOK '
    Workbooks.OpenXML "C:\Path\To\Output.xml", , xlXmlLoadImportToList

End Sub

Workbook Result

工作簿的结果