Access Cookbook - Ken Getz [261]
Excel exposes rich and varied inner workings via Automation, but taking advantage of those capabilities is nearly impossible without reference materials. This solution barely scratches the surface of what's available to you in Access from Excel. If you need to use the two products together, use the Object Browser in the Visual Basic Editor to explore the objects in the Excel object model. You can bring up the help topic for each object from within the Object Browser.
See Also
A good reference book for Excel programming is Writing Excel Macros by Steven Roman (O'Reilly). The Solution in Recipe 12.6 will give you a chance to explore a few of the more interesting corners of the Excel object model. For more information on sorting, using VBA, see Recipe 7.7 in Chapter 7.
12.4. Perform a Mail Merge from Access to Word
Problem
You'd like to be able to do a mail merge to Word using Access data, without having to launch the mail merge from Word using its mail merge features.
Solution
Access allows you to output data directly to any format using the DoCmd.OutputTo functionality. You can then run a mail merge from Word to a predefined Word template that contains the merge codes.
First you must create the Word template that holds your merge codes; then you can write the code in Access that performs the merge. The sample application 12-04.MDB contains a table and a query that retrieves the data to be sent to Word.
To perform a mail merge from Access to Word, follow these steps:
In Access, create the query that you will use for your data. Copy the rows from the datasheet view of the query and paste them into a Word document.
Save the Word document in the same folder as the Access database. The sample application uses the name qryCustomers.doc.
In Word, create a template by choosing File New Template from the menu. Fill in the plain text for your main merge document.
Choose Tools → Mail Merge from the menu to add the merge fields to the template. Use the Active Document option and select the Word document you created in Step 2. This will add the merge toolbar to your application.
Insert the merge codes for the fields in your template, then save the template in the same folder as qryCustomers.doc and the Access database.
In Access, write the code to perform the mail merge. Declare two module-level constants for the name of the template and the name of the query:
Private Const conTemplate As String = "acbMailMerge.dot"
Private Const conQuery As String = "qryCustomers"
Set a reference to the Word library by choosing Tools → References... and selecting the Word library from the list of objects, as shown in Figure 12-6.
Figure 12-6. Set a reference to the Word library
Create a procedure to perform the mail merge. Here's the complete listing:
Public Sub MailMerge( )
Dim strPath As String
Dim strDataSource As String
Dim doc As Word.Document
Dim wrdApp As Word.Application
On Error GoTo HandleErrors
' Delete the rtf file, if it already exists.
strPath = FixPath(CurrentProject.Path)
strDataSource = strPath & conQuery & ".doc"
Kill strDataSource
' Export the data to rtf format.
DoCmd.OutputTo acOutputQuery, conQuery, _
acFormatRTF, strDataSource, False
' Start Word using the mail merge template.
Set wrdApp = New Word.Application
Set doc = wrdApp.Documents.Add(strPath & conTemplate)
' Do the mail merge to a new document.
With doc.MailMerge
.OpenDataSource Name:=strDataSource
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
If .State = wdMainAndDataSource Then
.Execute
End If
End With
' Display the mail merge document.
wrdApp.Visible = True
ExitHere: