Output property to save as

Output property to save as

• John J Stephen

  Hi Brenda,
  
  Sorry for be slow in this reply.
  
  I think that some progress was did, but I don't know if this is the right way. I put the adodb in use to save my datas as an xls file but in the end my datas turned in some stranger charcters, something like . After this I added this line in the code - oStream.Type = 1, and no datas or characters has been showed.
  
  my purpose: save as an xsl file or create a output right to my sheet.
  
  
  The VBA code.
  
  Dim app As New MSXML2.DOMDocument30
  
  Dim xmlDom, xslDom, projDom, xslFileName As String
  
  Dim xslt As New MSXML2.XSLTemplate30
  Dim xslDoc As New MSXML2.FreeThreadedDOMDocument30
  Dim xmlDoc As New MSXML2.DOMDocument30
  Dim xslProc As IXSLProcessor
  Dim xmlout As New MSXML2.FreeThreadedDOMDocument30
  Dim oStream As New adodb.Stream
  Dim oRecord As New adodb.Recordset
  Set oStream = New adodb.Stream
  
  oStream.Type = 1 'Binary
  
  
  xslDoc.async = False
  xslDoc.Load "c:\compara.xsl"
  
  
  
  If (xslDoc.parseError.errorCode <> 0) Then
  Dim myErr
  Set myErr = xslDoc.parseError
  MsgBox ("Ha um erro " & myErr.reason)
  Else
  Set xslt.stylesheet = xslDoc
  xmlDoc.async = False
  xmlDoc.Load "C:\Instructional_program.xml"
  If (xmlDoc.parseError.errorCode <> 0) Then
  Set myErr = xmlDoc.parseError
  MsgBox ("You have error " & myErr.reason)
  Else
  oStream.Open
  
  Set xslProc = xslt.createProcessor()
  xslProc.input = xmlDoc
  'xslProc.Transform
  xslProc.output = oStream
  
  
  xslProc.Transform
  
  oStream.SaveToFile ("c:\Doc_out.xls")
  
  oStream.Close
  
  
  End If
  End If
  End Sub
  
  
  Thank you
  Marcos Hercules
  
  
  

• vinclaro001

  here's the latest from the engineer:

  I worked on the issue and got a sample code... on my machine it is working fine.

  To demonstrate how we can convert a XML document to a Microsoft Excel document I will be using two sample files

  1. authors.xml

  < xml version="1.0" >

  <NewDataSet>

  <authors>

  <au_id>172-32-1176</au_id>

  <au_lname>White</au_lname>

  <au_fname>Johnson</au_fname>

  <phone>408 496-7223</phone>

  <address>10932 Bigge Rd.</address>

  <city>Menlo Park</city>

  <state>CA</state>

  <zip>94025</zip>

  <contract>true</contract>

  </authors>

  <authors>

  <au_id>213-46-8915</au_id>

  <au_lname>Green</au_lname>

  <au_fname>Marjorie</au_fname>

  <phone>415 986-7020</phone>

  <address>309 63rd St. #411</address>

  <city>Oakland</city>

  <state>CA</state>

  <zip>94618</zip>

  <contract>true</contract>

  </authors>

  <authors>

  <au_id>238-95-7766</au_id>

  <au_lname>Carson</au_lname>

  <au_fname>Cheryl</au_fname>

  <phone>415 548-7723</phone>

  <address>589 Darwin Ln.</address>

  <city>Berkeley</city>

  <state>CA</state>

  <zip>94705</zip>

  <contract>true</contract>

  </authors>

  </NewDataSet>

  2. authors.xsl

  <xsl:stylesheet version="1.0"

  xmlns="urn:schemas-microsoft-com:office:spreadsheet"

  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"

  xmlns:msxsl="urn:schemas-microsoft-com:xslt"

  xmlns:user="urn:my-scripts"

  xmlns:o="urn:schemas-microsoft-com:office:office"

  xmlns:x="urn:schemas-microsoft-com:office:excel"

  xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" >

  <xsl:template match="/">

  <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"

  xmlns:o="urn:schemas-microsoft-com:office:office"

  xmlns:x="urn:schemas-microsoft-com:office:excel"

  xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"

  xmlns:html="http://www.w3.org/TR/REC-html40">

  <xsl:apply-templates/>

  </Workbook>

  </xsl:template>

  <xsl:template match="/*">

  <Worksheet>

  <xsl:attribute name="ss:Name">

  <xsl:value-of select="local-name(/*/*)"/>

  </xsl:attribute>

  <Table x:FullColumns="1" x:FullRows="1">

  <Row>

  <xsl:for-each select="*[position() = 1]/*">

  <Cell><Data ss:Type="String">

  <xsl:value-of select="local-name()"/>

  </Data></Cell>

  </xsl:for-each>

  </Row>

  <xsl:apply-templates/>

  </Table>

  </Worksheet>

  </xsl:template>

  <xsl:template match="/*/*">

  <Row>

  <xsl:apply-templates/>

  </Row>

  </xsl:template>

  <xsl:template match="/*/*/*">

  <Cell><Data ss:Type="String">

  <xsl:value-of select="."/>

  </Data></Cell>

  </xsl:template>

  </xsl:stylesheet>

  Create a macro in Microsoft Excel

  Sub Macro1()

  Dim xslt As New MSXML2.XSLTemplate30

  Dim xslDoc As New MSXML2.FreeThreadedDOMDocument30

  Dim xmlDoc As New MSXML2.DOMDocument30

  Dim xslProc As IXSLProcessor

  xslDoc.async = False

  xslDoc.Load "C:\authors.xsl"

  If (xslDoc.parseError.errorCode <> 0) Then

  Dim myErr

  Set myErr = xslDoc.parseError

  MsgBox ("You have error " & myErr.reason)

  Else

  Set xslt.stylesheet = xslDoc

  xmlDoc.async = False

  xmlDoc.Load "C:\authors.xml"

  End If

  If (xmlDoc.parseError.errorCode <> 0) Then

  Set myErr = xmlDoc.parseError

  MsgBox ("You have error " & myErr.reason)

  Else

  Set xslProc = xslt.createProcessor()

  xslProc.input = xmlDoc

  xslProc.transform

  Const XMLFileName As String = "C:\Output.xls"

  Dim FileNum As Integer

  FileNum = FreeFile ' next file number

  Open XMLFileName For Output As #FileNum ' creates the file if it doesn't exist

  Print #FileNum, xslProc.output ' write information to the file

  Close #FileNum ' close the file

  Workbooks.OpenXML "C:\Output.xls"

  End If

  End Sub

  -brenda (ISV Buddy Team)

  
  
  

• svxtc

  per the support engineer:

  I would need some clarification in the problem stated in the email. Could you show me the full code. I just want to know how the Object “xmlDoc “is created and populated with data

  -brenda (ISV Buddy Team)

  
  
  

• Andrei S.

  Hi,
  
  now it’s all O.K, the problem was in my xslt. There was an </HTML> in the wrong place.
  The code works fine, it shows  the result of an XSLT operation that compares  two XML documents through a VBA application.
  Thanks Brenda, thanks all
  
  
  Marcos Hercules Santos
  
  
  

• Blackwood

  
  Hi ,
  
  I just don't know what it's happening with this code, when I changed it to save like a txt or html format I got it according to the output in my XML file, but when I save it like an xls format still not appearing.
  
  
  
  
  
  
• Output property to save as