Editing text of an XML file

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
 

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