wanting to save HTML format in one sheet

  • Thread starter Marcos Hercules
  • Start date
M

Marcos Hercules

Hi all,

Recently I did a VBA code that works with MSXML and save the result in
one another *.xls file, something like this

<?xml version="1.0" encoding="ISO-8859-1"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<table border="1"
width="70%"><tr><th>discipline</th><th>suggests</th><th>available</th><th>non
available</th></tr><tr><td><b>1995-1999</b></td><td
align="center">2</td><td align="center">1</td><td align="center">1
</td></tr><tr><td><b>2000-2005</b></td><td align="center">2</td><td
align="center">2</td><td align="center">0 </td></tr></table>
</html>

and now I wish that this same result it was showed right to my sheet1
and no more saving like other file.

See'm mycode


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
 
T

Tim Williams

You could just open the data xml file and walk through the "records", writing the content to the sheet.
Hard to offer more without seeing the input.

Tim
 
M

Marcos Hercules

I tried something, but no success

below goes my code



Dim dados As MSXML2.DOMDocument30
Dim receb 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
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 ("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


'xmlDoc.transformNodeToObject xslDoc, oStream

'oStream.SaveToFile ("c:\test200.xls")

Dim sHTML As String
sHTML = xmlDoc.transformNode(xslDoc)




oStream.Close

End If
End If

End Sub

the oStream gives me another file, but this is not more important, I
need to produce a output right to my sheet1 in this worksheet, then I
don't know what change but I did a String (sHTML), that receive a
tranformation from a XSLT. The output is an HTML format.

But still having no idea how to produce this. I found methods that
gave me a output to a cell, this ain't the ideal.

Tim Williams escreveu:
 
M

Marcos Hercules

I tried something, but no success

below goes my code



Dim dados As MSXML2.DOMDocument30
Dim receb 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
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 ("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


'xmlDoc.transformNodeToObject xslDoc, oStream

'oStream.SaveToFile ("c:\test200.xls")

Dim sHTML As String
sHTML = xmlDoc.transformNode(xslDoc)

'need to create a output from sHTML to sheet1, no more to other *xls
file


oStream.Close

End If
End If

End Sub

the oStream gives me another file, but this is not more important, I
need to produce a output right to my sheet1 in this worksheet, then I
don't know what change but I did a String (sHTML), that receive a
tranformation from a XSLT. The output is an HTML format.

But still having no idea how to produce this. I found methods that
gave me a output to a cell, this ain't the ideal.

Tim Williams escreveu:
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top