Solved

250 Points WORD mailmerge with VB

Posted on 1998-05-29
11
472 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
11 Comments
 
LVL 1

Expert Comment

by:JayMerritt
Comment Utility
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
Comment Utility
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 150 total points
Comment Utility
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
 

Author Comment

by:Wigmans
Comment Utility
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
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:Wigmans
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Bought This Question.
0
 

Expert Comment

by:kbalaraju
Comment Utility
Can we print a word document issuing any VB Commands?
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

763 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now