Visual Basic find and replace from Access table to Word document

I need to take text from a Rich Text field in Access and paste it into Word using Visual Basic (VBA). I have the code working to do this. The problem is that the formatting tags display in Word rather than the actual formatting.

How do I get the format of the pasted text to display rather than the tags?

Here's the function I'm using along with the code that calls the function:

Public Sub replaceTextFromClipboard(match As String, ByRef wordDoc As Word.Document)
    With wordDoc.Content.Find
        .ClearFormatting
        .Replacement.ClearFormatting
        .Replacement.Font.Size = 11
        .Replacement.Font.Name = "Cambria"
        .Replacement.Font.Color = wdColorAutomatic
        .Text = match
        .Replacement.Text = "^c"
        .Forward = True
        .Wrap = wdFindContinue
        .Format = True
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
        .Execute replace:=wdReplaceAll
    End With
End Sub


                clipboard.SetText cleantext(rst2!Dep_Elig_Def)
                clipboard.PutInClipboard
                Call replaceTextFromClipboard("<<DependentsAnswer>>", wordDoc)
                clipboard.Clear
PerfishentAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
First, this will only work if the Word doc is in the new format: .docx

And you are correct, you cannot just "dump" html into word and have it display properly...
But you can simply drag and drop it from Access to Word, and the formatting carries over just fine...

So lets see if an expert can help further...

JeffCoachman
0
PerfishentAuthor Commented:
I am using the .docx Word format. I have a reference set to the Microsoft Word 14.0 Object Library. I'm using the following code to open the Word doc:

Dim wordApp As Word.Application
Dim wordDoc As Word.Document
Set wordDoc = wordApp.Documents.Open(curPath & "\Templates\ACA Health Exchange Notice - Client Plan.docx")

When I close and save the document, I'm using the following code:

docName2 = curPath & "\Notices\" & rst!Company_No & "-" & rst!Client_No & "-" & rst!emp_no & " " & cleantext(rst!FName) & " " & cleantext(rst!Lname) & ".docx"
           
wordDoc.SaveAs2 docName2, wdFormatDocumentDefault
0
PerfishentAuthor Commented:
I'm pretty sure the clipboard.settext(text, format) method needs to have the format set to Rich Text. But I can't find the correct way to set the format to Rich Text. All of the examples I've seen are for VB.NET.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Jeffrey CoachmanMIS LiasonCommented:
Without access to your app, this is difficult to visualize or test...

1. What is this "cleantext()" function..?
If you just do this what happens:
    clipboard.SetText rst2!Dep_Elig_Def


2. You are using these lines:
        .ClearFormatting
        .Replacement.ClearFormatting
What is the result if you comment out these lines...

JeffCoachman
0
PerfishentAuthor Commented:
Cleantext() is a custom function that trims the text, replaces apostrophes, and removes any non-printable characters.

If I remove the ClearFormatting lines, the replacement text still displays the html code the same as before:

divfont face=Arial size=2 color=blackThe Subscribers legal spouse or an unmarried dependent child of the Subscriber or the Subscribers spouse. The term child includes any of the following/font/divdivfont face=Arial size=2 color=black&nbsp/font/divul lifont face=Arial size=2 color=blackA natural child, including a newborn child from the moment of birth./font/li lifont face=Arial size=2 color=blackA stepchild./font/li lifont face=Arial size=2 color=blackA legally adopted child./font/li lifont face=Arial size=2 color=blackA child placed for adoption, beginning from/font/li lifont face=Arial size=2 color=blackThe moment of birth, if placement for adoption occurs within 30 days of the childs birth./font/li lifont face=Arial size=2 color=blackThe date of placement, if placement for adoption occurs within 31 or more days after the childs birth./font/li lifont face=Arial size=2 color=blackA child for whom legal guardianship has been awarded to the Subscriber or the Subscribers spouse./font/li/uldivfont
0
PerfishentAuthor Commented:
The application takes text stored in a Rich Text memo field and pastes the text into Word. I have some placeholder text in Word that shows where the replacement text should go.
0
Jeffrey CoachmanMIS LiasonCommented:
So let wait for a Word Expert to chime in...

I just suggested those things as they seemed like a good place to start investigating...
0
PerfishentAuthor Commented:
I never could get the clipboard.settext() method to preserve the rich text formatting entered into the Memo fields in Access. I had to change the fields to plain text in order to get the application to work correctly.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PerfishentAuthor Commented:
No one else proposed a viable solution.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.