?
Solved

250 Points WORD mailmerge with VB

Posted on 1998-05-29
11
Medium Priority
?
485 Views
Last Modified: 2008-02-01
This is the second time that i ask this question.
The first time was on 5/12/98 (100 points).
The one who nows a solution for this problem must send 2 times to get 250 points.

Question:
How do i mail merge (Word 7.0 or Word97) with VB5 ?
It is possible to do this with access. So it must be possible with VB.
The template file 'template.doc' containts fields who are stored in the access database work.mdb in the table adres.

Example:
The file template.doc is the main document
Work.mdb within the table adres is my data source.
I have already opened the table adres and its points to the record (name,adres, cyty) i want to substitute this values in the fields of the template.doc and save the template.doc as new.doc.
After the fields are subsituted i don't want to close the document new.doc for futher editing.
If there is already an active Word-document i don't want to start a second word application but i want to use 'open' in Word.
0
Comment
Question by:Wigmans
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 1

Expert Comment

by:JayMerritt
ID: 1461988
If you've got it working in Access, the code is virtually identical.  Paste it in to a VB module and see what happens.
0
 

Expert Comment

by:sye
ID: 1461989
I have called Word from VB apps supplying the doc name and a macro to run in Word that updates fields in the document obtained from an Access DB. The VB code I used is:

                     ***create the Object Name
                     Dim Worddocs as Object
                    ***create the word 95 object
               Set worddocs = CreateObject("word.basic")
                         **** display the Word Window
                  worddocs.appshow
                  With worddocs
                         *****open the desired documenbt
                     .fileopen "c:\repgen\repgen.txt"
                          ****call the macro to make changes    
                     .toolsmacro Name:="Apath", run:=True
                          ***save the document
                          ***lcsave as is a variable used to pass
                          ***a new document name
                     .filesaveas lcsaveas
                  End With


0
 
LVL 5

Accepted Solution

by:
bin_huwairib earned 450 total points
ID: 1461990
Wigmans,

I think the trick here is how to save the new document with the data you've pointed with your VB code, the following example demonstrate how to sync between the document data source (after opening the template document) and the pointed record set then save it in a new document.

Example
=======
 Dim WordBasicObject As Object
 Dim DB As Database
 Dim RS As Recordset
 
 Set DB = OpenDatabase("c:\db1.mdb")
 Set RS = DB.OpenRecordset("table1")
 RS.MoveNext           'Point to the second record
 
 Set WordBasicObject = CreateObject("Word.Basic")
 WordBasicObject.FileOpen "c:\doc2.doc"       'Open the template document
 Call WordBasicObject.MailMergeFindRecord(RS(0).Value, Field:="Name")   'Move the data source record to the first matched record with the field "Name"
 Call WordBasicObject.MailMergeViewData(True)   'Make sure to display the data
 WordBasicObject.FileSaveAs "c:\New.Doc"
 WordBasicObject.FileClose
 WordBasicObject.FileExit
 
 Set WordBasicObject = Nothing
 RS.Close
 DB.Close


Best regards
Bin Huwairib
0
Independent Software Vendors: 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!

 

Author Comment

by:Wigmans
ID: 1461991
Bin Huwairib

I tried your solution.
I get the error message: 'Runtime error: 51. Word reached the end of the database. Do you want to continue searching at the beginning?' on line Call WordBasicObject.MailMergeFindRecord(RS(2).Value, Field:="Naam")
Layout RS:
(0)=Custid
(1)=Firstname
(2)=Naam
(3)=adres
(4)=city
With the debugger i see that the value of RS(2) is correct.
If i delete this line, i get no errors.
But i get always the same record in 'new.doc',
even if i do RS.MoveLast or RS.MoveFirst
0
 
LVL 5

Expert Comment

by:bin_huwairib
ID: 1461992
Wigmans,

Try opening the record set as dynaset and the use MailMergeGotoRecord instead of MailMergeFindRecord, so it will look like this:

 Dim WordBasicObject As Object
 Dim DB As Database
 Dim RS As Recordset
 
 Set DB = OpenDatabase("c:\db1.mdb")
 Set RS = DB.OpenRecordset("table1", dbOpenDynaset)
 RS.MoveNext
 
 Set WordBasicObject = CreateObject("Word.Basic")
 WordBasicObject.FileOpen "c:\doc2.doc"
 WordBasicObject.MailMergeGotoRecord RS.AbsolutePosition + 1
 Call WordBasicObject.MailMergeViewData(True)
 WordBasicObject.FileSaveAs "c:\New.Doc"
 WordBasicObject.FileClose
 WordBasicObject.FileExit
 
 Set WordBasicObject = Nothing
 RS.Close
 DB.Close

In case you faced the same problem I suggest to post your code which will help to find out the problem faster.

Bin Huwairib
0
 

Author Comment

by:Wigmans
ID: 1461993
Bin Huwairib

This solution works almost.
1. If i do mail merge by hand, merge ask me for a new document.
The result is a substituted letter new.doc without a link to the access database (something like SendToNewDocument + SaveAs NewDocument).
Your example create a newfile with a link to the access database.
I don't want a link to the database because this is very slow.

2.
I Get an automation error on line: WordBasicObject.FileExit
After deleting this i get a good resultut.
Why do you put this line in the function ?

3. This is an extra question you don't need to answer but i can try. Why tack it a long time to load temp.doc. In the statusbar you see DDE-messages for the access-database. If have only 15 records in my database ?


Sub MergeWord2(sNaam As String)
Dim WordBasicObject As Object

Set WordBasicObject = CreateObject("Word.Basic")
WordBasicObject.FileOpen "c:\data\werk\temp.doc" 'Open the template document

Call WordBasicObject.MailMergeFindRecord(sNaam, Field:="Naam") 'Move the data source record to the first matched record with the field "Name"
Call WordBasicObject.MailMergeViewData(True) 'Make sure to display the data
WordBasicObject.FileSaveAs "c:\data\werk\New.Doc"
WordBasicObject.FileClose
' WordBasicObject.FileExit

Set WordBasicObject = Nothing

End Sub
0
 
LVL 5

Expert Comment

by:bin_huwairib
ID: 1461994
Wigmans,

1- If you don't want the new document to be linked to a database you have to set the destination to a document then save it as a normal word document without having a mail merge fields in it, you can do that by the following code:

Private Sub Command1_Click()
 Dim WordBasicObject As Object
 Dim DB As Database
 Dim RS As Recordset
 
 Set DB = OpenDatabase("c:\db1.mdb")
 Set RS = DB.OpenRecordset("table1", dbOpenDynaset)
 RS.MoveNext
 
 Set WordBasicObject = CreateObject("Word.Basic")
 WordBasicObject.FileOpen "c:\doc2.doc"
 WordBasicObject.MailMerge CheckErrors:=1, Destination:=0, MergeRecords:=1, _
 From:=RS.AbsolutePosition + 1, To:=RS.AbsolutePosition + 1, MailMerge:=1
 WordBasicObject.FileSaveAs "c:\New.Doc"
 WordBasicObject.FileClose
 WordBasicObject.FileClose
 WordBasicObject.FileExit
 
 Set WordBasicObject = Nothing
 RS.Close
 DB.Close
End Sub

2- I've used WordBasicObject.FileExit to make sure that Winword application instance has completely removed form the memory because Set WordBasicObject = Nothing statement does not do that.

3- While opening the template document Word will try to connect to the template's database which will load MSAccess and then it execute a query to retrieve data to be fetched in the document, as you see all this operations will definitely take time (it depends on your machine performance).

Bin Huwairib
0
 

Author Comment

by:Wigmans
ID: 1461995
Bin Huwairib

This solution is good.
Only the second FileClose (doc2) cann't be closed because it is
changed. I get a message to save it.
What is the statement to close a changed file without sacing it?

Where can i read/get the syntax of 'Word.basic',
so that i can see what statements are possible

Don't you forget to send me the first my question (5 mai 98) to get the other 100 points.
0
 
LVL 5

Expert Comment

by:bin_huwairib
ID: 1461996
You can try this

WordBasicObject.Fileclose 0

Anyway check the following site it is pretty good:

http://www.microsoft.com/WordDev/Articles/word2vba.htm

Bin Huwairib
0
 
LVL 13

Expert Comment

by:Mirkwood
ID: 1461997
Bought This Question.
0
 

Expert Comment

by:kbalaraju
ID: 2209900
Can we print a word document issuing any VB Commands?
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying 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

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

719 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