Document Creation

Privateer · #1 06-19-2013, 11:59 AM

We are trying to create a combo box that will populate various fields with information about the selected employee. The document is a check list of required actions by HR and supervisors of all employes separating from the company. We have a list of employee names, departments, hire dates, etc in Excel. We want a combo box on the Word document where we select the person's name and the various powerfields get the related information.

So, is this a mail merge with one record? How do you filter a merge to one person

Or is there a way to link with Excel using a combo box?

Anyway, maybe I should ask a more general question; what is the best way to accomplish this?

We are using Windows 7 with Office 10. I am an expert with VBA, but I have not used word in over a decade.

Thanks

Privateer

Charles Kenyon · #2 06-20-2013, 05:15 AM

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.

Privateer · #3 06-20-2013, 01:18 PM

Thanks for the reply. You also addressed the issue of the data being attached to the template which we were worried about. I will give this a shot. Thanks again.

Charles Kenyon · #4 06-20-2013, 08:15 PM

I save the template not attached to the database. That way I don't get the idiot query about whether I want to connect.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Calendar Creation	sjvjoe	Publisher	1	07-14-2016 06:32 PM
Help with Complex Table Creation	saquib	Word	0	02-12-2013 06:28 AM
Creation and Merge trouble	Etera	Mail Merge	0	07-27-2011 02:57 PM
Label Creation	speloquin	Word	1	05-27-2011 03:08 PM
Report creation.	Igtech	Excel	1	04-02-2010 03:33 PM