asked on

Use VBA to strip RTF formatting going from RTF field in Access to Form field in Word

I have an Access form with an RTF field on it. I need to place the contents of this field in a word document. The Word doc is protected and uses a lot of fields.

I use Dlookup to get the contents of the field in access and put it in a string variable.

Function GetWorkOrderFieldVal(iTktIn As Integer, sFieldToGet As String, sReturn As String) As Boolean
Dim vRes As Variant
vRes = DLookup("[" & sFieldToGet & "]", "[Common]", "[Ticket] = " & iTktIn)
If Not IsVoid(vRes) Then
    sReturn = CStr(vRes)
    GetWorkOrderFieldVal = True
Else
    sReturn = "Err"
    GetWorkOrderFieldVal = False
End If
End Function

Open in new window

I open the Word doc like this:

Dim WordApp As word.Application, WordDoc As word.Document
Set WordApp = New word.Application
Set WordDoc = WordApp.Documents.Open("DaDocFileName", , True, , , , , , , , , True)
WordApp.visible = True

Dim fld As word.Field, sRet As String
Set fld = WordDoc.Fields(1)
bRes = GetWorkOrderFieldVal(iTktIn, "GeneralNotes", sRet)
fld.Result.Text = sRet

Open in new window

As you may see, when I run the Dlookup function it grabs all the text in the table field including all the RTF tags.

Question: Is there a way to have all but the line break tags stripped from the string variable?

I know there is a function called PlainText, but it removes the line breaks in the string. The only formatting I need IS the line breaks.

Thanks!

als315

Try to add CRLF after line break (</p> tag) and then use PlainText:
sReturn = PlainText(Replace(sReturn,"</p>","</p>" & Chr(13) & Chr(10)))

AviationAce

ASKER

I just tried that. PlainText strips the line feed.

? PlainText("One" & " Two")
One Two

? PlainText("One" & vbcrlf & " Two")
One  Two

? PlainText("One" &  Chr(13) & Chr(10) & " Two")
One  Two

Open in new window

als315

Look at sample (Query1 make Field2 from field Field1 in table Table1)
DBformat.mdb

AviationAce

ASKER

I think I know why your code is working. In the query the function is working directly the RFT formatted field. In my code I put the contents of an RTF field in to a String Variable. This may be why your method isn't working.

ASKER CERTIFIED SOLUTION

als315

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

AviationAce

ASKER

This will obviously be a work in progress, but for the time being, this function appears to be getting the job done.

Function StripRTFformatting(sRTFin As String) As String
Dim sRet As String
sRet = Replace(sRTFin, "<div>", "", , , vbTextCompare)
'sRet = Replace(sRet, "</div>", vbCrLf, , , vbTextCompare)
sRet = Replace(sRet, vbCrLf & vbCrLf, vbCrLf, , , vbTextCompare)
sRet = Replace(sRet, "</div>", "", , , vbTextCompare)
sRet = Replace(sRet, "&nbsp;", " ")
sRet = Replace(sRet, "&nbsp;", " ")
'Debug.Print sRTFin
'Debug.Print "--------------------------------------------------------------"
'sRet = PlainText(sRet)
'Debug.Print sRet
StripRTFformatting = sRet
End Function

Open in new window

Thanks for the help!

AviationAce

ASKER

I wonder why the PlainText function strips all the LineFeeds from the string.