Solved

250 Points WORD mailmerge with VB

Posted on 1998-05-29
11
478 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
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 150 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

810 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