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
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