How to insert RTF from Word Doc into Access Table Field

Question

In my code I open a word document from MS Access and read out a certain section of the document. If I was doing this and would only have to store the plain text, this would be easy enough... but I need to keep all the formatting.

From what I've read on the web, Access 2007 and up can easily store Rich Text Formatt (RTF). I adjusted my Access Table to have the specified field defined as "Memo" and "Rich Text". So the field itself is set up and working properly.

Copying and pasting some data manually gets stored as it should.

My question to which I can't seem to find an answer: How do you do it using Code???

Here is the relevant code snippet for what I have so far:

Set doc = New Word.Application
doc.Visible = True
Set dcmt = doc.Documents.Open(sPathTemplate)
Set sectn = dcmt.Sections(2)

Dim x As String
sAnalystText = sectn.Range.Tables(1).cell(1, 1).Range.FormattedText

rsComments.AddNew
rsComments![ISIN] = "Fake_ISIN"
rsComments![Fund_Selection] = 1
rsComments![Long Comment Exec] = sAnalystText
rsComments.Update

I have tried using both .Text and .FormattedText but neither works. Any help much appreciated!

I found a workaround solution by copying and pasting the text from an access form and then pasting it into the word document — rohrl77
i'll try to dig it up for you... might be that I get to it tomorrow though — rohrl77
I actually had a similar problem later on and stumbled upon this solution... far superior to what I ended up doing: thesmileycoder.com/exporting-rich-text-to-excel you might be able to adapt it to Word. — rohrl77
I see, thanks for that link. But my problem is the other way around - I need to get formatted text from Word to Access. — Andre

user6351839 user6351839 · Accepted Answer · 2016-05-18T15:19:28

Dim rs As New ADODB.Recordset
Dim A() As Byte
Dim myrtf As Variant
Dim doact As Document
Set doact = ActiveDocument
myrtf = Null
ActiveDocument.Range(0, 100).Copy

Dim dc As New Document

dc.Activate


dc.Range(0, 0).PasteAndFormat wdFormatOriginalFormatting

dc.SaveAs2 FileName:="C:\x.rtf", FileFormat:=wdFormatRTF

dc.Close

   doact.Activate
  Open "C:\x.rtf" For Binary As #1
  ReDim A(LOF(1))
  While Not (EOF(1))
        Get #1, , A(i)
        i = i + 1

  Wend
   Close #1
    myrtf = A
conn.Open "Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\test.accdb"

strsql = "Insert into table ( rftfield) values ('" & myrtf & "')"

conn.Execute strsql

conn.Close

How to insert RTF from Word Doc into Access Table Field

1 Answers