title: xslt transformation in vba
body: <p>I am trying to generate an xml from a set of xpaths. This is related to <a href="https://stackoverflow.com/questions/22353758/generating-xml-from-xpath-using-xslt">Generating XML from xpath using xslt</a></p>
  
  <p>I found a XSLT that may solve my problem in this <a href="https://stackoverflow.com/a/11406308/1788964">answer to another question</a></p>
  
  <pre><code>&lt;xsl:stylesheet version="2.0"
       xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
       xmlns:xs="http://www.w3.org/2001/XMLSchema"
       xmlns:my="my:my"&gt;
       &lt;xsl:output omit-xml-declaration="yes" indent="yes"/&gt;
  
       &lt;xsl:key name="kNSFor" match="namespace" use="@of"/&gt;
       &lt;xsl:variable name="vStylesheet" select="document('')"/&gt;
  
       &lt;xsl:variable name="vPop" as="element()*"&gt;
          &lt;item path="/create/article/@type"&gt;richtext&lt;/item&gt;
          &lt;item path="/create/article/@lang"&gt;en-us&lt;/item&gt;
          &lt;item path="/create/article[1]/id"&gt;1&lt;/item&gt;
          &lt;item path="/create/article[1]/description"&gt;bar&lt;/item&gt;
          &lt;item path="/create/article[1]/name[1]"&gt;foo&lt;/item&gt;
          &lt;item path="/create/article[1]/price[1]/amount"&gt;00.00&lt;/item&gt;
          &lt;item path="/create/article[1]/price[1]/currency"&gt;USD&lt;/item&gt;
          &lt;item path="/create/article[1]/price[2]/amount"&gt;11.11&lt;/item&gt;
          &lt;item path="/create/article[1]/price[2]/currency"&gt;AUD&lt;/item&gt;
          &lt;item path="/create/article[2]/id"&gt;2&lt;/item&gt;
          &lt;item path="/create/article[2]/description"&gt;some name&lt;/item&gt;
          &lt;item path="/create/article[2]/name[1]"&gt;some description&lt;/item&gt;
          &lt;item path="/create/article[2]/price[1]/amount"&gt;00.01&lt;/item&gt;
          &lt;item path="/create/article[2]/price[1]/currency"&gt;USD&lt;/item&gt;
  
          &lt;namespace of="create" prefix="ns1:"
                     url="http://predic8.com/wsdl/material/ArticleService/1/"/&gt;
          &lt;namespace of="article" prefix="ns1:"
                     url="xmlns:ns1='http://predic8.com/material/1/"/&gt;
          &lt;namespace of="@lang" prefix="xml:"
                     url="http://www.w3.org/XML/1998/namespace"/&gt;
          &lt;namespace of="price" prefix="ns1:"
                     url="xmlns:ns1='http://predic8.com/material/1/"/&gt;
          &lt;namespace of="id" prefix="ns1:"
                     url="xmlns:ns1='http://predic8.com/material/1/"/&gt;
       &lt;/xsl:variable&gt;
  
       &lt;xsl:template match="/"&gt;
        &lt;xsl:sequence select="my:subTree($vPop/@path/concat(.,'/',string(..)))"/&gt;
       &lt;/xsl:template&gt;
  
       &lt;xsl:function name="my:subTree" as="node()*"&gt;
        &lt;xsl:param name="pPaths" as="xs:string*"/&gt;
  
        &lt;xsl:for-each-group select="$pPaths" group-adjacent=
              "substring-before(substring-after(concat(., '/'), '/'), '/')"&gt;
          &lt;xsl:if test="current-grouping-key()"&gt;
           &lt;xsl:choose&gt;
             &lt;xsl:when test=
                "substring-after(current-group()[1], current-grouping-key())"&gt;
  
               &lt;xsl:variable name="vLocal-name" select=
                "substring-before(concat(current-grouping-key(), '['), '[')"/&gt;
  
               &lt;xsl:variable name="vNamespace"
                             select="key('kNSFor', $vLocal-name, $vStylesheet)"/&gt;
  
  
               &lt;xsl:choose&gt;
                &lt;xsl:when test="starts-with($vLocal-name, '@')"&gt;
                 &lt;xsl:attribute name=
                   "{$vNamespace/@prefix}{substring($vLocal-name,2)}"
                      namespace="{$vNamespace/@url}"&gt;
                   &lt;xsl:value-of select=
                    "substring(
                         substring-after(current-group(), current-grouping-key()),
                         2
                               )"/&gt;
                 &lt;/xsl:attribute&gt;
                &lt;/xsl:when&gt;
                &lt;xsl:otherwise&gt;
                 &lt;xsl:element name="{$vNamespace/@prefix}{$vLocal-name}"
                            namespace="{$vNamespace/@url}"&gt;
  
                      &lt;xsl:sequence select=
                       "my:subTree(for $s in current-group()
                                    return
                                       concat('/',substring-after(substring($s, 2),'/'))
                                     )
                       "/&gt;
                   &lt;/xsl:element&gt;
                &lt;/xsl:otherwise&gt;
               &lt;/xsl:choose&gt;
             &lt;/xsl:when&gt;
             &lt;xsl:otherwise&gt;
              &lt;xsl:value-of select="current-grouping-key()"/&gt;
             &lt;/xsl:otherwise&gt;
           &lt;/xsl:choose&gt;
           &lt;/xsl:if&gt;
        &lt;/xsl:for-each-group&gt;
       &lt;/xsl:function&gt;
  &lt;/xsl:stylesheet&gt;
  </code></pre>
  
  <p>I am trying to transform this in VBA using code from <a href="http://www.oreillynet.com/xml/blog/2005/04/transforming_xml_in_microsoft.html" rel="nofollow noreferrer">http://www.oreillynet.com/xml/blog/2005/04/transforming_xml_in_microsoft.html</a></p>
  
  <p>I got an error<br>
  <img src="https://i.stack.imgur.com/e7gxY.png" alt="error when using transformNodeToObject"></p>
  
  <pre class="lang-vb prettyprint-override"><code>Private Sub Transform(sourceFile, stylesheetFile, resultFile)
  
      Dim source As New MSXML2.DOMDocument30
      Dim stylesheet As New MSXML2.DOMDocument30
      Dim result As New MSXML2.DOMDocument30
  
      ' Load data.
      source.async = False
      source.Load sourceFile
  
      ' Load style sheet.
      stylesheet.async = False
      stylesheet.Load stylesheetFile
  
      If (source.parseError.ErrorCode &lt;&gt; 0) Then
         MsgBox ("Error loading source document: " &amp; source.parseError.reason)
      Else
          If (stylesheet.parseError.ErrorCode &lt;&gt; 0) Then
              MsgBox ("Error loading stylesheet document: " &amp; stylesheet.parseError.reason)
          Else
              ' Do the transform.
              source.transformNodeToObject stylesheet, result
              result.Save resultFile
          End If
      End If
  
  End Sub
  </code></pre>
  
  <p>I also tried to use transformNode instead to transformNodeToObject. I got</p>
  
  <blockquote>
    <p>object doesn't support this property or method</p>
  </blockquote>
  
  <p><img src="https://i.stack.imgur.com/HnmQn.png" alt="transformNode error"></p>
  
  <pre class="lang-vb prettyprint-override"><code>Private Sub Transform(sourceFile, stylesheetFile, resultFile)
      Dim source As New MSXML2.DOMDocument60
      Dim stylesheet As New MSXML2.DOMDocument60
      Dim result As New MSXML2.DOMDocument60
  
      ' Load data
      source.async = False
      source.Load sourceFile
  
      ' Load style sheet.
      stylesheet.async = False
      stylesheet.Load stylesheetFile
  
      If (source.parseError.ErrorCode &lt;&gt; 0) Then
         MsgBox ("Error loading source document: " &amp; source.parseError.reason)
         Else
      If (stylesheet.parseError.ErrorCode &lt;&gt; 0) Then
            MsgBox ("Error loading stylesheet document: " &amp; stylesheet.parseError.reason)
         Else
            ' Do the transform.
            source.transformNode (stylesheet)
            'result.Save resultFile
      End If
      End If
  End Sub
  </code></pre>