Excel VBA - Insert XML From String

Question

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

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.

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.

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

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).

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

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.

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.

In case you need it, here is some sample 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">(718) 555-1212</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">(718) 555-1213</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">(718) 555-1214</Fld>
      <Fld Nm="Agent State">CA</Fld>
      <Fld Nm="Agent ZIP Code">00003</Fld>
    </Row>
  </RepeatingFieldSet>
</RpcData>

And my XSL file:

<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!

Parfait · Accepted Answer · 2016-06-14 02:20:31Z

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"?>:

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:

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

answered Jun 14, 2016 at 2:20

Parfait

108k19 gold badges102 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ImagineMBE Over a year ago

Thanks! The first method gives me a user prompt to choose the XSL, and then a message that the file is in a different file format than the extension. Luckily, though, the second method loads the transformed XML correctly without a user prompt. But I was really hoping to be able to insert the XML directly from a string. I have four XMLs that I'm trying to load into different sheets in the workbook. I suppose it just isn't possible?

Parfait Over a year ago

If xsl is declared in the process-instruction of xml <?xml-stylesheet ... ?>, you should not be prompted. The xsl must reside in same directory as xml or child folders href='childfolder/script.xsl. Also, simply save XML string to file as OpenXML runs one wb at a time requiring .xml files. Consider combining four worksheets into one.

Collectives™ on Stack Overflow

Excel VBA - Insert XML From String

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related