Access Cookbook - Ken Getz [262]
Set doc = Nothing
Set wrdApp = Nothing
Exit Sub
HandleErrors:
Select Case Err.Number
Case 53 ' File not found.
Resume Next
Case Else
MsgBox Err.Number & ": " & Err.Description
Resume ExitHere
End Select
End Sub
Create the FixPath procedure to handle any backslashes in the pathname:
Private Function FixPath(strPath As String) As String
If Right(strPath, 1) = "\" Then
FixPath = strPath
Else
FixPath = strPath & "\"
End If
End Function
Test the procedure by positioning your cursor anywhere in the MailMerge procedure and pressing the F5 key.
Discussion
Microsoft Word exposes an Application object, which you can use to launch Word, and a Document object, which you can use to open a new Word document. Once you've launched Word, you can use all its capabilities from your Access application. The following sections outline the steps involved in communicating with Word via Automation.
Starting the connection with Word for Windows
To be able to work with Word from Access, you must create an object variable to refer to the Word Application object. You also need a Document variable to work with a specific Word document. The following code fragment defines these variables:
Dim doc As Word.Document
Dim wrdApp As Word.Application
The next step is to delete any previously existing data source documents:
strPath = FixPath(CurrentProject.Path)
Kill strPath & conQuery & ".doc"
If the document doesn't exist, the error handler will simply resume on the next statement and create a new document containing the data from the query using the OutputTo method of the DoCmd object:
DoCmd.OutputTo acOutputQuery, conQuery, _
acFormatRTF, strPath & conQuery & ".doc", False
Performing the mail merge
To launch Word and create a new document based on the mail merge template, set the Application object to a new instance of Word.Application. Set the Document object to create a new document using the Application's Add method, basing it on your template:
Set wrdApp = New Word.Application
Set doc = wrdApp.Documents.Add(strPath & conTemplate)
Once the document is open, use the Document object's MailMerge method to merge the data 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
In Access 2002 and later you must use the .OpenDataSource method in your code, but this isn't required in Access 2000.
Finishing the mail merge
To display the Word documents, set the Application object's Visible property to True:
wrdApp.Visible = True
Once the Word document is displayed, clean up by setting the Word object variables to Nothing. This frees up the memory and system resources:
Set doc = Nothing
Set wrdApp = Nothing
You'll see both the new document, named Document1 (based on the template), and the actual merge documents. You can save the merge documents or print them from Word.
12.5. Add an Item to the Startup Group
Problem
As part of your application, you would like to be able to allow users to add an application to the Startup menu so that your application will start up when Windows does. You just can't figure out how to put the information into the Startup group. Is there a way to communicate between Access and the Windows shell so you can do this?
Solution
This is a case where the old technology called DDE comes in handy. The Windows shell accepts commands using DDE that allow you to create and delete groups and items. You can also retrieve lists of existing groups and items within those groups. This solution explains most of the Windows shell's DDE interface.
To test out the DDE interface, load and run the form frmShell from 12-05.MDB. This form, shown in Figure 12-7, allows you to view groups and their items, create and delete groups and items, and display a particular group. It will decide whether to use the group/item