RTF into word through VBA (or how to use pastespecial)

[Technokrat]

Some background: we have a VB6 app that allows the user to enter data into several different Rich Text Boxes. The RTB data is then written into a SQl Server database. If you look at the text/string in the database it contains the Rich Text Formatting characters.

We want to use a Word (sadly 97) Macro to query the RTF from the database and place it appropriately in report template.

One possible solution would be to write each response to a RTF file, and then import each RTF file into the Word document. However, I'm convinced there has to be a better way to do this.

I know the SELECTION has the PASTESPECIAL method which allows you to paste using RTF, but I don't know how to get the string read from the Db onto the clipboard for PASTESPECIAL to be used. How is the clipboard accessed through VBA?

 

[johnwm]

The clipboard object can be accessed directly:

Clipboard.Clear
Clipboard.SetText
Clipboard.GetData
Clipboard.SetData
Clipboard.GetText



________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

For tsunami relief donations

'If we're supposed to work in Hex, why have we only got A fingers?'

http://www.essexsteam.co.uk

for steam enthusiasts

 

[CubeE101]

I think you want to use something like...

Clipboard.SetText RichTextBox1.TextRTF, [b]vbCFRTF[/b]

Then just use the Paste command in word...
such as:

Selection.Paste

Or...

Dim wdApp as object
Set wdApp = getobject(,"Word.Application")
wdApp.Selection.Paste

Have Fun, Be Young... Code BASIC
-Josh

http://cubee101.topcities.com

PROGRAMMER: Red-eyed, mumbling mammal capable of conversing with inanimate objects.

 

[Technokrat]

I tried accessing the Clipboard directly, as suggested. But, here is the problem. Unlike VB, when you're writing a macro in VBA, the clipboard object is not available. This produces an object required error.

From within VB, I used the definition to determine that the clipboard object is a member of GLOBALS found in the VB6.OLB library. I then tried to include the VB6.OLB under tools references, so the clipboard object would be available within the macro. But this causes the macro to die an ugly death where I actually have to reboot the system before I am able to access WORD again.

Any further thoughts...

 

[vladk]

http://www.cpearson.com/excel/clipboar.htm

 

[Technokrat]

vladk, thank you for the link.

Will that work for the text with Rich Text Formatting?

 

[Technokrat]

Alright, I got it!!!

Download the following module to access the clipboard

oopicide.mcglothin.info/ Oopic_5.1.22/Source/modClipboard.bas

As the module comes it facilitates the cut & paste of plain text. So we have to make a couple of modifications to maintain our RTF.

First of all, CF_RTF is not in the set of standardized formats, Microsoft refers to it as a synthesized format. what this means to us, is that we will need to register the format so it will be recognized. Add the following to the header section of modClipboard.bas

Declare Function RegisterClipboardFormat _
    Lib "user32" _
    Alias "RegisterClipboardFormatA" _
(ByVal lpString As String) As Long

In SetClipboardData we will add a line, and change a line, but first dim CF_RTF as Long.

     CF_RTF = RegisterClipboardFormat("Rich Text Format")
            
     ' Copy the data to the Clipboard.
     lngClipMem = WinSetClipboardData(CF_RTF, lngHoldMem)

The change occurs in the 2nd line where CF_TEXT is replaced with our newly registered CF_RTF.

That accomplishes our goal. We may now query the database to retrieve the RTF fields as strings, paste them to the clipboard using SetClipboardData, and then paste them into the word report using the selection.pastespecial.

The following is a crude example to illustrate the process (sans the db references):

Sub testpastertf()
Dim strSelection As String

Dim rtb As New RichTextBox

strSelection = "{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fswiss\fcharset0 Arial;}{\f1\froman\fprq2\fcharset0 Garamond;}}" & _
"{\colortbl ;\red0\green128\blue0;\red255\green0\blue0;\red0\green0\blue128;}" & _
"{\*\generator Msftedit 5.41.15.1507;}\viewkind4\uc1\pard\f0\fs20\par" & _
"\par" & _
" This is \cf1 Some \cf2 fancy \cf3\b\i\f1\fs36 RTF \cf0\b0\i0\f0\fs20 text\par}"

rtb.TextRTF = strSelection

SetClipboardData rtb.TextRTF

pasteRTF

End Sub

Sub pasteRTF()

    Selection.PasteSpecial Link:=False, DataType:=wdPasteRTF, Placement:= _
        wdInLine, DisplayAsIcon:=False
End Sub

To test the code, create a word template/macro paste in the above code. Add the modClipboard.bas module with the changes we discussed. Make sure under Tools - References you have added "Microsoft Rich Textbox Control" (you may need to browse to the OCX). And your good to go...

Happy coding.