?
Solved

RTF Formatting in SQL Server and Word 2000

Posted on 2008-11-04
11
Medium Priority
?
645 Views
Last Modified: 2012-05-05
Hi
I have an Access 2000 database with an RTF textbox. I would like to store RTF data in a SQL Server which I appear to have achieved as the field has the usual RTF tags (e.g. {rtf1\ansi... etc) in the text field. However, I want to merge this into a template in Word 2000 and all I get is the RTF tag in the form described (e.g. not the formatting but the whole RTF string). Is it possible to merge RTF into a Word 2000 document? If not, are there any other suggestions for formatting text in Access or a .NET application and then merging with a Word 2000 template?

Thanks.
0
Comment
Question by:gycitsupport
  • 6
  • 5
11 Comments
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 22881937
This is not that easy because you have to strip the header.

The functions below do this and concatenate a series of "stripped" rtf string into one full rtf string.
You should be able to tweak them to fit your purpose.

/gustav
Public Function TrimTextBodyRTF(strTextRTF As String) As String
 
' Extract RTF body of full RTF formatted string.
'
' 2003-11-01. Gustav Brock. Cactus Data ApS. CPH.
 
  ' RTF escape/control char.
  Const cstrRTFEscape       As String = "\"
  ' RTF code identifying RTF control codes leading RTF body.
  Const cstrRTFBodyPointer  As String = cstrRTFEscape + "pard"
  ' Char identifying hex code for non-ascii char.
  Const cstrRTFByteChar     As String = "'"
  ' Header string for hex code for non-ascii char.
  Const cstrRTFByteHeader   As String = cstrRTFEscape + cstrRTFByteChar
  ' Char identifying start of RTF body if first RTF char is an ascii char.
  Const cstrRTFBodyHeader   As String = " "
  ' Char closing RTF body.
  Const cstrRTFBodyEnd      As String = "}"
  
  Dim strRTF                As String
  Dim strEnd                As String
  Dim lngPos                As Long
  Dim lngPosAsc             As Long
  Dim lngPosHex             As Long
  Dim lngEnd                As Long
  Dim lngLen                As Long
  Dim lngChr                As Long
  
  If Len(strTextRTF) > 0 Then
    ' Locate RTF body pointer.
    lngPos = InStr(strTextRTF, cstrRTFBodyPointer)
    If lngPos > 0 Then
      ' Locate start of body if first char is an ascii char.
      lngPosAsc = InStr(lngPos, strTextRTF, cstrRTFBodyHeader)
      ' Check if first char in RTF body is a non-ascii char.
      lngPosHex = InStr(lngPos, strTextRTF, cstrRTFByteHeader) - 1
      lngPos = 0
      If lngPosAsc > 0 Then
        If lngPosHex > 0 Then
          If lngPosHex < lngPosAsc Then
            lngPos = lngPosHex
          Else
            lngPos = lngPosAsc
          End If
        Else
          lngPos = lngPosAsc
        End If
      Else
        lngPos = lngPosHex
      End If
      If lngPos > 0 Then
        strRTF = Mid(strTextRTF, 1 + lngPos)
        ' Locate position of RTF end marker (closing bracket).
        lngChr = Asc(cstrRTFBodyEnd)
        lngLen = Len(strRTF)
        lngEnd = 1
        While Asc(Right(strRTF, lngEnd)) <> lngChr And lngEnd < lngLen
          lngEnd = lngEnd + 1
        Wend
        If lngEnd = lngLen Then
          ' RTF end marker was not found.
          lngPos = 0
        Else
          ' Calculate length of RTF body.
          lngPos = lngLen - lngEnd
        End If
        ' Trim RTF body.
        strRTF = Left(strRTF, lngPos)
      End If
    End If
  End If
  
  TrimTextBodyRTF = strRTF
  
End Function
 
Public Function ConcatenateTextRTF() As Boolean
 
' Concatenate RTF body strings to one fully formatted RTF file.
'
' Error handling is missing.
'
' 2003-11-01. Gustav Brock. Cactus Data ApS. CPH.
' 2003-11-02. RTF code \fprq0 added; \viewkind, \uc1 8Unicode), \f0 removed.
'             RTF code \plain added to reset in-line font settings.
 
  ' RTF escape/control char.
  Const cstrRTFEscape       As String = "\"
  ' RTF header. Adjust codepage and language code and font as needed.
  Const cstrRTFBodyHeader   As String = "{\rtf1\ansi\ansicpg1252\deflang1030\deff0{\fonttbl{\f0\fswiss\fprq0\fcharset0 Arial;}}\pard "
  ' RTF closing bracket.
  Const cstrRTFBodyEnd      As String = "}"
  ' RTF code to reset font settings.
  Const cstrRTFFontPlain    As String = cstrRTFEscape + "plain"
  ' Filename of finished RTF document.
  Const cstrRTFFile         As String = "c:\winnt\temp\test.rtf"
  
  Dim dbs                   As Database
  Dim rst                   As Recordset
  
  Dim strRTF                As String
  Dim intFile               As Integer
  
  Set dbs = CurrentDb
  Set rst = dbs.OpenRecordset("Tabel1")
 
  ' Set RTF header.
  strRTF = cstrRTFBodyHeader
  ' Concatenate RTF bodies.
  With rst
    While .EOF = False
      If Not IsNull(!MemoRTF) Then
        strRTF = strRTF + cstrRTFFontPlain + Space(1) + TrimTextBodyRTF(!MemoRTF)
      End If
      .MoveNext
    Wend
    .Close
  End With
  ' Append RTF closing bracket.
  strRTF = strRTF + cstrRTFBodyEnd
  
  ' Write RTF file.
  intFile = FreeFile
  Open cstrRTFFile For Output As #intFile
  Print #intFile, strRTF
  Close #intFile
 
  Set rst = Nothing
  Set dbs = Nothing
  
  ConcatenateTextRTF = True
  
End Function

Open in new window

0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 22881946
Oh, this is VBA code. Still, it should be quite easy to convert to VB.NET or C#.

/gustav
0
 

Author Comment

by:gycitsupport
ID: 22884172
Hi
Thanks for those code samples, I've been adding them to my code to see if I can produce what I need.  I tried the stripped header and it did bring through the text, although without the formatting and it still had the end strips of "\b0 \f1" etc. when merged.  Basically I have an rtf string like this :

{\rtf1\ansi\ansicpg1252\deff0{\fonttbl{\f0\froman\fprq2\fcharset0 Times New Roman;}{\f1\fnil\fcharset0 MS Sans Serif;}}\viewkind4\uc1\pard\lang2057\b\f0\fs24 I am bold\b0\f1\fs17 \par }

and need to get out "I am bold" in bold format.

Hope this makes sense and thanks for any help.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 52

Expert Comment

by:Gustav Brock
ID: 22884656
You could replace this line:
   lngPosAsc = InStr(lngPos, strTextRTF, cstrRTFBodyHeader)
with this:
   lngPosAsc = lngPos + 4

/gustav
0
 

Author Comment

by:gycitsupport
ID: 22884706
Hi again
I don't think that has helped at all unfortunately - I'm still getting RTF formatting in the string. Am I right in thinking that this function will strip out the text from the RTF formatting? I need to keep the formatting and apply it to the merged phrase within my Word template which is also where I think there may be problems.  I'm beginning to think this isn't connected so much at the Access end as the Word merge end.
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 22884747
That is quite different from "merging" which you requested.

RTF text is RTF encoded which includes encoded single characters and cannot be "stripped" by a simple parsing.
You must tell Word that this text is RTF - then Word, by definition, knows all about how to handle that.

Or - if you wish to extract the unformatted text - you will have to run your RTF text through an RTF to Text converter like the one shown below.

/gustav

Public Function ConvRTFtoText( _
                ByVal strRTF As String, _
                Optional booSingleRun As Boolean) _
                As String
 
' Converts RTF formatted string to plain text using RTF ActiveX control.
' When finished, Rich Text object is removed if booSingleRun is True.
'
' 2001-05-27. Cactus Data ApS, CPH.
  
  Static objRTF As Object
  Static booErr As Boolean
  Dim strText   As String
  
  On Error GoTo Err_ConvRTFtoText
  
  If objRTF Is Nothing Then
    Set objRTF = CreateObject("RICHTEXT.RichtextCtrl")
  End If
  
  With objRTF
    .TextRTF = strRTF
    strText = .Text
  End With
  
  If booSingleRun = True Then
    Set objRTF = Nothing
  End If
  
  ConvRTFtoText = strText
  
Exit_ConvRTFtoText:
  Exit Function
 
Err_ConvRTFtoText:
  If booErr = False Then
    MsgBox "Error " & Err.Number & ". " & Err.Description & "!", _
           vbExclamation + vbOKOnly, _
           "Rich Text converter"
    ' Only show error message once per session.
    booErr = True
  End If
  Resume Exit_ConvRTFtoText
  
End Function

Open in new window

0
 

Author Comment

by:gycitsupport
ID: 22884765
Thanks for your help with this. I definitely want the formatting. Do you have any idea on how to "tell" Word that the string is in an RTF format for the merge? I have read worrying posts that may indicate that this isn't possible without saving the Word file as in RTF beforehand which isn't an option for me at the moment.

Thanks again,
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 22884877
No, but in Excel you have an option like "Copy Special" to specify how to copy and paste - wonder if something like that should exist in Word as well?

However, my previous suggestion returned:

\lang2057\b\f0\fs24 I am bold\b0\f1\fs17 \par

Isn't that close enough? If you wish to remove specific code, it could be done with Replace().

/gustav
0
 

Author Comment

by:gycitsupport
ID: 22884925
Hi
Your function works fine in stripping out the header and the ending but it's really the merge that's the problem now as all I get it the line you describe in my document ,rather than "I am bold" in a bold font style.  I may have to look into using the clipboard or paste as special within the code itself.
0
 
LVL 52

Accepted Solution

by:
Gustav Brock earned 600 total points
ID: 22885276
OK. Then it would be the

  .. .Range.Copy

and

  .. .Range.PasteSpecial with DateType = wdPasteRTF

methods.

/gustav
0
 

Author Closing Comment

by:gycitsupport
ID: 31513130
Thanks for all your help. I shall try that.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In a previous video Micro Tutorial here at Experts Exchange (http://www.experts-exchange.com/videos/1358/How-to-get-a-free-trial-of-Office-365-with-the-Office-2016-desktop-applications.html), I explained how to get a free, one-month trial of Office …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

755 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question