PDA

View Full Version : VBA Find and replace in XML/Text file



jtolj
07-12-2007, 09:11 AM
Hi everyone,
I'm a VBA novice to say the least, so I apologize if this is an absurdly simple problem. I've scoured forums and usenet and have not found a solution.

I'm working on a VBA script to export some data from an excel spreadsheet into an XML file and then do an XSL transform so that the XML can be imported into Final Cut Pro (a video editing application). This I have accomplished, however the one issue remaining is dealing with the "double escaped" characters that Excel seems to output.

I need to use line breaks for final cut, however "&13;" in excel turns into "&13;" when exported into XML. I have tried to deal with this in my XSLT, but have not been successful.

What I was thinking was that perhaps there is a way to just automate a find and replace (replace & with &) as the final step in my VBA. Is there a way to do a text string find and replace on a variable set up as an MSXML2.DOMDocument30?

Thanks in advance for any advice. My code to date is below.





Sub ExportToXML()
'
' ExportToXML Macro

Worksheets("XML").Activate

' Set up variables

Dim XMLFilename As String
Dim XSLFilename As String
Dim ResultFilename As String
Dim source As New MSXML2.DOMDocument30
Dim stylesheet As New MSXML2.DOMDocument30
Dim result As New MSXML2.DOMDocument30

' Assign filenames

XMLFilename = "c:\lineupxml\" + Range("B2").Value
XSLFilename = "c:\lineupxml\xsl\transform.xsl"
ResultFilename = XMLFilename + ".xml"

' Export raw XML from Excel data

ActiveWorkbook.XmlMaps("xmeml_Map").Export URL:=XMLFilename

'Load data into source variable

source.async = False
source.Load XMLFilename

' Load XSL into stylesheet variable and check validity

stylesheet.async = False
stylesheet.Load XSLFilename

If (source.parseError.errorCode <> 0) Then
MsgBox ("Error loading source document: " & source.parseError.reason)
Else
If (stylesheet.parseError.errorCode <> 0) Then
MsgBox ("Error loading stylesheet document: " & stylesheet.parseError.reason)
Else

' Do XSL transform and save to file

source.transformNodeToObject stylesheet, result
result.Save ResultFilename

End If
End If

' Clean up temp file

Kill XMLFilename

End Sub

lucas
07-12-2007, 09:39 AM
Not sure if this is what you need but try this:
Dim cel As Range
For Each cel In ActiveSheet.UsedRange
cel = Replace(cel, "&" & "amp;", "&")
Next

jtolj
07-12-2007, 11:29 AM
Not sure if this is what you need but try this:
Dim cel As Range
For Each cel In ActiveSheet.UsedRange
cel = Replace(cel, "&" & "amp;", "&")
Next
Thanks for your reply. Unfortunately, that doesn't do what I need. The escape character is correct in excel, it is incorrect in the XML file that I've exported through VBA.

For example, my excel cell has:


=A1 & "&#13 ;" & B1
(ignore extra space before semicolon, I got an actual line feed in the post if I left that correct)

When I export to XML I get:


<parameter>A1 &amp;#13; B1</parameter>
In other words, the ampersand in my escape character (line feed) is escaped again. So I actually need to do the find/replace in either the variable that holds the XML in my VBA or to the actual output XML file.

Unless of course, someone knows how to keep Excel from escaping the ampersand upon XML export.

Thanks!

stanl
07-12-2007, 11:40 AM
I think what he needs is

result = Replace(result, "&" & "amp;", "&")

just prior to saving the file.

I had a similar situation once; just ran everything through jscripts unescape() function. Stan

jtolj
07-12-2007, 12:58 PM
I think what he needs is
result = Replace(result, "&" & "amp;", "&")

just prior to saving the file.


That seems like exactly what I need, but when I try it I get:

Run-time error '438':
Object doesn't support this property or method

Perhaps because result is an MSXML2.DOMDocument30?

mvidas
07-12-2007, 01:31 PM
result is an object, so it wouldn't be able to work.
Mind you, I have never worked with a domdocument30, but you could try using result.text, result.innerhtml, or even result.body.innerhtml and see if that allows a replace.

Worse comes to worse, you could just open the file, replace the text, and put it back in the file:Dim vFF As Long, TempStr As String
vFF = FreeFile
Open ResultFilename For Binary As #vFF
TempStr = Space$(LOF(vFF))
Get #vFF, , TempStr
Close #vFF
TempStr = Replace(TempStr, "&" & "amp;", "&")
Open ResultFilename For Output As #vFF
Print #vFF, TempStr
Close #vFFAlso, its more common practice to use & to concatenate strings instead of +

EDIT: Fixed the above sub to avoid the double text

jtolj
07-13-2007, 11:58 AM
Worse comes to worse, you could just open the file, replace the text, and put it back in the file:Dim vFF As Long, TempStr As String
vFF = FreeFile
Open ResultFilename For Binary As #vFF
TempStr = Space$(LOF(vFF))
Get #vFF, , TempStr
Close #vFF
TempStr = Replace(TempStr, "&" & "amp;", "&")
Open ResultFilename For Output As #vFF
Print #vFF, TempStr
Close #vFF


This worked perfectly (although I realize it's probably not the most elegant solution). Thanks so much for everyone's help!