Thread: [Solved] Document Creation
View Single Post
 
Old 06-20-2013, 05:15 AM
Charles Kenyon Charles Kenyon is offline Windows Vista Office 2010 32bit
Moderator
 
Join Date: Mar 2012
Location: Sun Prairie, Wisconsin
Posts: 9,083
Charles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant future
Default

Assuming that all of your information is in one Excel file.

I do this regularly using a mailmerge.

I have various templates with the mailmerge fields in them. There is an AutoNew macro in these templates that attaches the Excel file as a data source and calls up the built-in dialog to pick a record.

Code:
Sub AttachClients()
'   Written by Charles Kenyon
'   19 April 2005 revised 15 December 2006
'
'   Requires WorkGroupPath function
'
'   Makes activedocument a mailmerge (letter) document and
'   attaches Clients_Merge.xls from Parts folder of Workgroup Templates folder.
'
'   Then displays search dialog and goes to client, makes sure merge info is
'   displayed instead of merge codes.
'
    On Error Resume Next
    MergeFieldUnlockAllStory 'if merge fields locked, unlock
'
'   Name of file
    Dim strFileName As String
    Dim strProvider As String
    strFileName = WorkGroupPath & "Parts\Merge Data\Clients_Merge.xls"
'
'   Attach Merge list
    ActiveDocument.MailMerge.OpenDataSource strFileName, , , False, _
        True, False, "", "", False, "", "", "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=strFileName;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:" _
        , "SELECT * FROM `Clients$`", "", , wdMergeSubTypeAccess
'
'   Show merge data
    ActiveDocument.MailMerge.ViewMailMergeFieldCodes = False
'
'   Find client
    Application.Dialogs(wdDialogMailMergeFindRecipient).Show
End Sub
The WorkgroupPath function gives a string for the Workgroup folder path.
Reply With Quote