Display XML Line Breaks in Microsoft Word Content Controls

[cdr_greg]

Hi,

I am writing a process where I extract record data (using ADODB) from Excel or Access and store it in a Microsoft Word Document CustomXMLPart.

I then map document content controls to the CustomXMLNodes to display the result. All is working very well except when the Access or Excel record fields contain line breaks or tabs.

Here is an example of the result if the record field contains a line break:

I am currently resolving this issue by running the extracted data string through a function that replaces the tabs and line breaks with a code string before storing the value in the CustomXMLNode:

Function fcnStringToXML(ByVal strPassed As String) As String
Dim strDigit As String, lngIndex As Long
For lngIndex = 0 To 31
If InStr(strPassed, ChrW(lngIndex)) > 0 Then
strDigit = "&0x" & Right$("0" & Hex(lngIndex), 2) & ";"
strPassed = Replace(strPassed, ChrW(lngIndex), strDigit)
End If
Next
Debug.Print strPassed
fcnStringToXML = strPassed
lbl_Exit:
Exit Function
End Function

... then after mapping the content control, I use the following to replace the code with the appropriate character to display:

oCC.Range.Text = Replace(oCC.Range.Text, "&0x0D;&0x0A;", "&0x0A;")
oCC.Range.Text = Replace(oCC.Range.Text, "&0x0D;", Chr(13))
oCC.Range.Text = Replace(oCC.Range.Text, "&0x0A;", Chr(11))
oCC.Range.Text = Replace(oCC.Range.Text, "&0x09;", Chr(9))

This results in the "?" being replaced with a Chr(11) and the line break appears in the resulting document.

Questions:

1. Is this an efficient way to handle this issue? Is there something better?
2. Are there other characters that may be a problem that I have yet to encounter?
3. If this is not the appropriate forum for this type of issue, can you point me to a better place to ask?

Thank you.