250 Points WORD mailmerge with VB

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.
WigmansAsked:
Who is Participating?
 
bin_huwairibConnect With a Mentor Commented:
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
 
JayMerrittCommented:
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
 
syeCommented:
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
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
WigmansAuthor Commented:
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
 
bin_huwairibCommented:
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
 
WigmansAuthor Commented:
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
 
bin_huwairibCommented:
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
 
WigmansAuthor Commented:
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
 
bin_huwairibCommented:
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
 
MirkwoodCommented:
Bought This Question.
0
 
kbalarajuCommented:
Can we print a word document issuing any VB Commands?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.