from transform to output, how to display

[Marcos Hercules Santos]

Hi all,

my question is about this VBA code:

Dim xslt As New MSXML2.XSLTemplate40
Dim xslDoc As New MSXML2.FreeThreadedDOMDocument40
Dim xmlDoc As New MSXML2.DOMDocument40
Dim xslProc As IXSLProcessor
Dim paramxml As MSXML2.DOMDocument40
xslDoc.async = False
xslDoc.Load "c:\compara.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:\Instructional_program.xml"
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
MsgBox xslProc.output 'here, how to produce a output right to a
sheet??

End if
End if


through this method a message shows me a result, that I think is a HTML
output.
Now I'd wish to know how this line xslProc.output can give me a
method to save like an excel file or display the results in a sheet??

Thanx

Marcos Hercules

[Joe Fawcett]

Post by Marcos Hercules Santos
Hi all,
Dim xslt As New MSXML2.XSLTemplate40
Dim xslDoc As New MSXML2.FreeThreadedDOMDocument40
Dim xmlDoc As New MSXML2.DOMDocument40
Dim xslProc As IXSLProcessor
Dim paramxml As MSXML2.DOMDocument40
xslDoc.async = False
xslDoc.Load "c:\compara.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:\Instructional_program.xml"
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
MsgBox xslProc.output 'here, how to produce a output right to a
sheet??
End if
End if
through this method a message shows me a result, that I think is a HTML
output.
Now I'd wish to know how this line xslProc.output can give me a
method to save like an excel file or display the results in a sheet??
Thanx
Marcos Hercules

To a sheet of what?
If you set your xslProc.output to be a DomDocument, assuming it is XML, then
you can use its Save method to save to a file.
Otherwise it will be utf-16 string, you'll have to clarify what you need to
do.

[Martin Honnen]

Post by Marcos Hercules Santos
MsgBox xslProc.output 'here, how to produce a output right to a
sheet??
through this method a message shows me a result, that I think is a HTML
output.
Now I'd wish to know how this line xslProc.output can give me a
method to save like an excel file or display the results in a sheet??

xslProc.output
gives you a string with the transformation result, you would then need
to check Excel API if it has methods to allow you to insert that string
into a sheet or load that string as a sheet.
Alternatively you could transform to a file and load that with Excel, to
do that you would need to set e.g.
xslProc.output = New ADODB.Stream
before you call transform and then use the stream method to save the
stream to a file and load that file within Excel.

[Marcos Hercules Santos]

Hi Martin,
I' ve been trying to obtain some success but, just a little evolution.
I did the code this way

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

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

Set xslProc = xslt.createProcessor()
xslProc.input = xmlDoc
'xslProc.Transform
xslProc.output = oStream


xslProc.Transform

oStream.SaveToFile ("c:\Doc_out.xls")


End If
End If
End Sub

-----------------------------------------------------------------

... and now I got in this xls file strange characters, something like
this

ℼ佄呃偙⁅瑨汭倠䉕䥌⁃ⴢ⼯㍗⽃䐯䑔䠠䵔⁌⸴‰牔湡

Could you look at my code once again and tell me what's going wrong?

Thanks

Post by Marcos Hercules Santos

Post by Marcos Hercules Santos
MsgBox xslProc.output 'here, how to produce a output right to a
sheet??
through this method a message shows me a result, that I think is a HTML
output.
Now I'd wish to know how this line xslProc.output can give me a
method to save like an excel file or display the results in a sheet??

xslProc.output
gives you a string with the transformation result, you would then need
to check Excel API if it has methods to allow you to insert that string
into a sheet or load that string as a sheet.
Alternatively you could transform to a file and load that with Excel, to
do that you would need to set e.g.
xslProc.output = New ADODB.Stream
before you call transform and then use the stream method to save the
stream to a file and load that file within Excel.
--
Martin Honnen --- MVP XML
http://JavaScript.FAQTs.com/

[Martin Honnen]

Post by Marcos Hercules Santos
Dim oStream As New adodb.Stream
Set oStream = New adodb.Stream
oStream.Open

Does it improve things if you set
oStream.Type = 1 'Binary
after the Open call?

[Marcos Hercules Santos]

Hey Martin,

What means this line you wrote?

after insert that nothing appear in my sheet.

Post by Martin Honnen

Post by Marcos Hercules Santos
Dim oStream As New adodb.Stream
Set oStream = New adodb.Stream
oStream.Open

Does it improve things if you set
oStream.Type = 1 'Binary
after the Open call?
--
Martin Honnen --- MVP XML
http://JavaScript.FAQTs.com/

[Martin Honnen]

Post by Marcos Hercules Santos
What means this line you wrote?

Post by Martin Honnen
oStream.Type = 1 'Binary

The ADODB.Stream can be of type text or binary, that line above sets the
stream type to a binary stream.

[Marcos Hercules Santos]

Hi, Martin

The Vba code works fine, the problem was in my XSLT file. There was an
</html> in the wrong place.

Thanx Martin

Post by Martin Honnen

Post by Marcos Hercules Santos
What means this line you wrote?

Post by Martin Honnen
oStream.Type = 1 'Binary

The ADODB.Stream can be of type text or binary, that line above sets the
stream type to a binary stream.
--
Martin Honnen --- MVP XML
http://JavaScript.FAQTs.com/