Using VBA to convert XML to CSV

Hi All,
 
Near the start of the thread below:
 
http://www.visualbasicscript.com/m_25624/tm.htm
 
mconnelly posts a bit of VBA and XSL code.
 
I'm testing this out as VBA is exactly what I want to use but I always just end up with an empty CSV file.
 
My XML looks like this:
 

 <?xml version="1.0" encoding="UTF-8"?>
   <form1>
     <CustomerName>Mr Testing</CustomerName>
     <CardID>123456789</CardID>
     <Address1>741 Sample Street</Address1>
     <Address2>Sampleton</Address2>
     <PostCode>SA3 6LE</PostCode>
     <HomePhone>0141 123 4567</HomePhone>
     <Mobile>011234 123456</Mobile>
     <Email>sample@sample.net</Email>
     <EmailNewsOptIn>1</EmailNewsOptIn>
     <FitnessGoals>Lose 24 stone before Friday</FitnessGoals>
  </form1>
 

 
My XSL file looks like this:
 

 <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
    xmlns:msxsl="urn:schemas-microsoft-com:xslt" 
                xmlns:user="mynamespace" 
                version="1.0"> 
 <msxsl:script implements-prefix="user"><![CDATA[ 
 ]]></msxsl:script> 
 <xsl:output method="text"/> 
 <xsl:template match="/"> 
    <xsl:apply-templates select="/*/row[1]" mode="names"/> 
    <xsl:apply-templates select="/*/row"/> 
 </xsl:template>    
 <xsl:template match="row" mode="names"> 
    <xsl:for-each select="*">"<xsl:value-of select="name()"/>"<xsl:if test="last() > position()">,</xsl:if></xsl:for-each><xsl:text>&#x20; 
 </xsl:text> 
 </xsl:template> 
 <xsl:template match="row"> 
    <xsl:for-each select="*"><xsl:value-of select="."/><xsl:if test="last() > position()">,</xsl:if></xsl:for-each><xsl:text>&#x20; 
 </xsl:text>   
 </xsl:template> 
 </xsl:stylesheet> 

 
And finally my VBA looks like:
 

 Sub xmltocsv()
 Dim oDOM As MSXML2.DOMDocument
 Dim oXML As MSXML2.DOMDocument
 Dim oXSL As MSXML2.DOMDocument
 Dim strHTML As String
 Dim strTransform As String
 Set oDOM = CreateObject("MSXML2.DOMDocument")
 oDOM.async = False
 oDOM.Load ("C:\Documents and Settings\Robert Peck\Desktop\PT Form\PT-Webform_data.xml")
 Set oXSL = CreateObject("MSXML2.DOMDocument")
 oXSL.async = False
 oXSL.Load "C:\Documents and Settings\Robert Peck\Desktop\PT Form\ConvertToCsv.xsl"
 'your XSLT stylesheet should be saved as unicode or UTF not ansii
 'note encoding instruction maybe needed for european language encoding  say swedish characters
 strTransform = oDOM.transformNode(oXSL)
 strHTML = "<?xml version='1.0' encoding=""ISO-8859-1""?>" & vbCrLf & _
            "<root>" & strTransform & "</root>"
 WriteFile "C:\Documents and Settings\Robert Peck\Desktop\PT Form\CSVversion" & Format(Now, "yyyymmddhhmmss") & ".csv", strTransform
 '
 'the above XSLT transform with xsl file converts this to a flat csv format file
 Set oDOM = Nothing
 Set oXML = Nothing
 Set oXSL = Nothing
 End Sub
 
 Public Sub WriteFile(ByVal sFileName As String, ByVal sContents As String)
 ' Dump XML  String to File for debugging
    Dim fhFile As Integer
    fhFile = FreeFile
   ' Debug.Print "Length of string=" & Len(sContents)
    Open sFileName For Output As #fhFile
    Print #fhFile, sContents;
    Close #fhFile
    Debug.Print "Out File" & sFileName
 End Sub
 

 
Can anyone help with why this isn't working?
 
cheers,
 
Bob
 

Sorry I don't have time to look at your code. However, in general, if you are using XSLT to do the transform, VBScript or any other language used is very simple to code - open/load XML doc into XML DOM object, same for XSL file, then do the transform of the XML with the XSL.

So any problems you get are from your XSL file, unless you have syntactically bad XML. You'll just have to keep on tweaking the XSL until you get the desired output. Go see some XSLT tutorials online to familiarize yourself with how to work with XSL.