Merge to Word Automation

[michaelfischer]

I am using access97 and desperatly need an example of code which will perform a merge
with a word 97 template and save the document automtically, without
showing the user that other applications are loading up in the background.

Have being trying for many days without success so would really appreciate any help with this matter.

Thanks in advance.

 

[Carol]

Here is code that I used. You can change the names to correspond to your fields and tables.

Make sure that you have a form letter already made up in word using a .dot template when using this function.

In your form, make a command button that when clicked would reference this function.

Public Function MergetoWord()
' This method creates a new document in MS Word 97 using Automation.
On Error Resume Next
Dim rsCust As Recordset, iTemp As Integer
Dim WordObj As Word.Application

Set rsCust = DBEngine(0).Databases(0).OpenRecordset("Customers", dbOpenTable)
rsCust.Index = "PrimaryKey"
rsCust.Seek "=", Forms!Orders![CustomerNumber]
If rsCust.NoMatch Then
MsgBox "Invalid customer", vbOKOnly
Exit Function
End If

DoCmd.Hourglass True

Set WordObj = GetObject(, "Word.Application")
If Err.Number <> 0 Then
Set WordObj = CreateObject("Word.Application")
End If

WordObj.Visible = True

WordObj.Documents.Add Template:="D:\office97\Templates\thanks.dot", NewTemplate:=False

WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="FullName"
WordObj.Selection.TypeText rsCust![ContactName]
WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="CompanyName"
WordObj.Selection.TypeText rsCust![CompanyName]
WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="Address1"
WordObj.Selection.TypeText rsCust![Address1]
WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="Address2"
If IsNull(rsCust![Address2]) Then
WordObj.Selection.TypeText ""
Else
WordObj.Selection.TypeText rsCust![Address2]
End If
WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="City"
WordObj.Selection.TypeText rsCust![City]
WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="State"
WordObj.Selection.TypeText rsCust![State]
WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="Zipcode"
WordObj.Selection.TypeText rsCust![Zipcode]
WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="PhoneNumber"
WordObj.Selection.TypeText rsCust![PhoneNumber]
WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="NumOrdered"
WordObj.Selection.TypeText Forms!Orders![Quantity]

WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="ProductOrdered"
If Forms!Orders![Quantity] > 1 Then
WordObj.Selection.TypeText Forms!Orders![Item] & "s"
Else
WordObj.Selection.TypeText Forms!Orders![Item]
End If

WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="FName"
iTemp = InStr(rsCust![ContactName], " ")
If iTemp > 0 Then
WordObj.Selection.TypeText Left$(rsCust![ContactName], iTemp - 1)
End If

WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="LetterName"
WordObj.Selection.TypeText rsCust![ContactName]

DoEvents
WordObj.Activate
WordObj.Selection.MoveUp wdLine, 6

' Set the Word Object to nothing to free resources
Set WordObj = Nothing

DoCmd.Hourglass False

Exit Function

TemplateError:
Set WordObj = Nothing
Exit Function

End Function


Good luck.

 

[JKB]

Carol, The code you've posted almost solves my similar problem, however being a relative novice to VBA, please elaborate a bit more on the following:

Where does the Function you wrote go? In a separate Module, or in the form?

What would the code be behind the command button to reference your function?

 

[Carol]

Copy and paste the function in a new module. You can call it whatever you like, but probably ModMergetoWord.

On your command button on the form, on the On Click, place the following:

=MergeToWord()

This should get you started. Don't forget to recompile.

[This message has been edited by Carol (edited 06-18-2001).]

 

[michaelfischer]

Thank you for youe prompt reply. I have had some success but am having trouble with memo fields as only 128 chars seem to be displayed. Any tips on where to look.

Many Thanks.

 

[JKB]

Carol,
When I compile ModMergeToWord, I receive a "User-Defined Type Not Defined" error, and highlighted is your code: Dim WordObj As Word.Application
When I compile the code for the command button on my form, I get the error message: "Expected line# or label, or statement or end of statement"
My code for the onclick event of the Cmd button is

Private Sub CmdMerge_Click()
=MergeToWord()
End Sub

I use A97, but advised earlier, I'm a novice. I pasted your code directly into the Mod and created a table and form with the exact same names as your fields. What am I doing wrong?

 

[Carol]

Make sure that the following references are checked within your database:

Microsoft Word
Microsoft Office
OLE Automation

also, type the =MergeToWord() beside the On Click, it is not an Event Procedure. This is how you would call a function.

Hope this helps you.

 

[JKB]

Carol,
I got close, but apparently the software on my PC was not installed with all of the options. When I attempt to define the References you indicated (Tools--References), the References button is not on my Tools drop down list. I have a manual that shows a photo of it being just above the ActiveX button, but there's not one on my PC. I have Office 97, Professional Edition, and have reinstalled it to try to pick up the file, but no luck. Thanks for your help, I'll keep looking.

 

[Carol]

If you cannot see it within your reference window then do a browse and add them.

Microsoft Word Office Library should be located within C:\Program Files\Microsoft Office\Office\MSWord9.OLB and
Microsoft Office Library should be located within C:\Program Files\Microsoft Office\Office\MS09.DLL

I am presently using Office 2000, so my above references reflect that version.

Good luck

 

[Jon Holmes]

JKB,

To access the references menu, you must open a module first and give it the focus. This can be one on the modules tab, or one behind a form or report.
The option isn't available when an object other than a module has the focus.

Jon

 

[JKB]

Thanks Jon,

But I'm about to decide that I am "out of my league" with Automation. I found the correct place to indicate the Reference and did so, and everyting in the module compiles, but the command button will not execute anything.

Private Sub CmdMerge_Click MergeToWord()

End Sub

Me thinks I need more study before I bug this forum anymore.

 

[charityg]

Private Sub CmdMerge_Click =MergeToWord()

And by the way, you aren't bugging anyone. If we didn't want to help you, we wouldn't log into this site in the first place.

I don't know about you, but I learn by DOING. A little over a year ago, I was a COMPLETE novice in Access and VBA, but because I tried things I'd never heard of (and didn't think could be done) I've become quite the little guru.

Chin up!! It WILL work, it just needed an adjustment. (See we offer moral support as well as technical support!)

Good luck, and let us know if you need anything else.

 

[CutAndPaste]

Remove Empty Bookmarks in Mail Merge?

Hi Carol, or any other person who knows more than me - and that's quite a few! ,

Just followed through Carol's answer in this thread and got my Merge to work fine (and first time!).

What I'd really like to know is how to remove blank lines from the address. Your code enters in a "" if the fields has no value but I want ot "move everything up" a line.

For example; if my fields are:
FirstName LastName
Address1
Address2
City
Zip

but the address I want ot send to Word has no Address2 line, In my letter I want to display:

FirstName LastName
Address1
City
Zip

Any ideas anyone?

tx

 

[CutAndPaste]

Found it!

After a little creative searching on this forum I came up with:


WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="Address2"
If IsNull(rsCust![Address2]) Then
WordObj.Selection.TypeText ""
WordObj.Selection.TypeBackspace ' Delete this bookmark!
Else
WordObj.Selection.TypeText rsCust![Address2]

Thanks anyway!