L
losbash
I am using Excel 03 to convert XML to Excel using an XSL (stylesheet)
to bring the XML into Excel valid structure. The code I am using works
around a known bug detailed in
http://support.microsoft.com/kb/307230/EN-US/
Sub Macro3()
'Load the XML and the XSL (the stylesheet).
Dim oXML As Object, oXSL As Object
Set oXML = CreateObject("MSXML.DOMDocument")
Set oXSL = CreateObject("MSXML.DOMDocument")
oXML.Load "c:\customers.xml"
oXSL.Load "c:\customers.xsl"
'Transform the XML using the stylesheet.
Dim sHTML As String
sHTML = oXML.transformNode(oXSL)
'Save the results to an HTML file.
Open "c:\customers.htm" For Output As #1
Print #1, sHTML
Close #1
'Automate Excel to open the HTML file.
Dim oApp As Excel.Application
Set oApp = CreateObject("excel.application")
oApp.Visible = True
oApp.Workbooks.Open "c:\customers.htm"
End Sub
What I have found using this code is that Excel still cannot open my
file - c:\customer.htm because of the way in which the XML header comes
across:
<?xml version="1.0" encoding="UTF-16"?>
I need to ensure that the header has spaces between the <? and the text
and the ?> and the text. In other words, it must look like this to
open:
<? xml version="1.0" encoding="UTF-16" ?>
How can I do this?
Thanks
to bring the XML into Excel valid structure. The code I am using works
around a known bug detailed in
http://support.microsoft.com/kb/307230/EN-US/
Sub Macro3()
'Load the XML and the XSL (the stylesheet).
Dim oXML As Object, oXSL As Object
Set oXML = CreateObject("MSXML.DOMDocument")
Set oXSL = CreateObject("MSXML.DOMDocument")
oXML.Load "c:\customers.xml"
oXSL.Load "c:\customers.xsl"
'Transform the XML using the stylesheet.
Dim sHTML As String
sHTML = oXML.transformNode(oXSL)
'Save the results to an HTML file.
Open "c:\customers.htm" For Output As #1
Print #1, sHTML
Close #1
'Automate Excel to open the HTML file.
Dim oApp As Excel.Application
Set oApp = CreateObject("excel.application")
oApp.Visible = True
oApp.Workbooks.Open "c:\customers.htm"
End Sub
What I have found using this code is that Excel still cannot open my
file - c:\customer.htm because of the way in which the XML header comes
across:
<?xml version="1.0" encoding="UTF-16"?>
I need to ensure that the header has spaces between the <? and the text
and the ?> and the text. In other words, it must look like this to
open:
<? xml version="1.0" encoding="UTF-16" ?>
How can I do this?
Thanks