Excel 2003 & XML Question

[Dreiding]

I've been able to create an excel XML export function. The first line of the
file is <xml version="1.0" ?>. The process works well except when some
non-xml acceptable characters are used. The export is always fine, but when
I view the xml file in any browser, the browser returns with errors. These
errors are due to have unacceptable characters. Ugh!

Are there any functions or method I can use to filter (replace) the bad
characters as the xml file is written?

Thanks,
- Pat

 

[Joel]

An XML file is a text file so you can always do the replace AFTER the file
has been exported. To do the replacement wile it was saving would mean you
would have to write you own routine to do the conversion to XML. Your other
choice would be to create a copy of the workbook and make the replacements in
the copy of the workbook before you saved. I think the copy method is the
best solution.

 

[Dreiding]

Thanks Joel,
However I have to automate this capability so fixing this manually is out of
the question. Do you know how I would fix the issues within the spreadsheet
before exporting?

Thanks.
- Pat

 

[Joel]

Had a small typo

Sub SaveXML()

ActiveSheet.Copy
Set NewSht = ActiveSheet
Set NewBK = ActiveWorkbook

fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="XML Files (*.xml), *.xml")
If fileSaveName = False Then
MsgBox ("Cannot Save file - Exiting Macro")
Exit Sub
End If


With NewSht.Cells

.Replace "ABC", "DEF"
.Replace ",", ";"
.Replace "^", "$"

End With

NewBK.SaveAs _
Filename:=fileSaveName, _
FileFormat:=xlXMLSpreadsheet

End Sub