Prepare documents for bulk changes: Merge a Word template and an Excel data source. Create a data source for a mail merge in Word Failed to open data source during a mail merge
Word can extract data to be merged from a variety of sources. Part of the Microsoft Office suite, Word easily accepts data from Outlook, Excel, and Access. Other sources can be used, including web pages, OpenDocument text files, and delimited data files saved as plain text. If you don't already have a data source, you can create one in Word.
Important: Accessing named data sources at an HTTP location is not supported. Before using the file as a data source for a mail merge, save it to your local hard drive.
Merge data sources
Listed below are some of the data sources that you can use for mail merge in Word.
Other database files
Other data sources can also be used for merging. To access these sources, run the Data Connection Wizard.
With your data source set up, you can create labels, envelopes, letters, and emails using mail merge.
additional information
Still have questions about a Word mail merge?
Help us improve Word
Do you have ideas for improving mail merge or other Word features? Share them on the page
Now you need to create a merge data source (addresses and surnames of the recipients of the letter).
1. In the section Select recipients window Merge in the task pane, select List creation (Figure 8.6).
Figure: 8.6. Window Select recipients master Merge
2. Click on the command Create a In chapter List creation... The dialog box shown in Fig. 8.7.
The merge data source is a database of records, each of which will be used to generate a specific copy of the letter using the main document. All records have the same data fields.
Some of the most common fields are pre-listed in the data source creation window. You can remove unnecessary fields from the database and add your own.
Figure: 8.7. Creating a data source
Other section items Select recipients allow you to select an existing database, previously created in Word or in an application like Access, or an address book as a data source.
Note The presence of extra fields in the data source does not affect the merge result in any way, but it slows down the program.
3. Click the button Customization... The dialog box shown in Fig. 8.8.
4. Highlight the item Appeal.
5. By clicking the button Delete erase the highlighted field.
Figure: 8.8. Setting up data source fields
Figure: 8.9. Dialogue window Merge recipients
6. Repeating steps 4 and 5, delete all fields except the Name, Surname, Organization, Address 1 and Index.
7. Click the button OK.
8. Click the button Close dialog boxes.
9. In the dialog box for saving the document that opens, enter the name Guests and click the button Save... The picture shown in Fig. 8.9 dialog box Merge recipients, which currently has no entries.
10. You will definitely need to enter information into the data source sometime. Click on the button Change ...to do it now. A form for entering data will open. Let's add some entries. Later, the merge will generate exactly as many copies of the master document as there are records in the data source.
11. Fill in the form fields.
12. To add another entry, click the button Create record.
13. Enter a few more records, transferring the information presented in table. 8.1.
TABLE 8.1. Merge data source
14. Click the button Closeto save the database and then on the button OK window Recipients.
Note To change the data source information, click in the toolbar Merge on the button Recipients, and then in the opened dialog window - on the button Edit... The data source form will open. To navigate through the source records, use the buttons The first, Back to, Forward, The last at the bottom of the form.
Figure: 8.10. Toolbar Merge
Recently in one organization I saw a wild picture :)
The company moved to a new building - from the Moscow region to Moscow. This is, accordingly, about changing the essential terms of the employment contract for all employees. According to the procedure, employees must be notified against signature of the upcoming move, and then conclude with all additional agreements to labor contracts. The organization employs about 1.5 thousand people, the personnel department must urgently prepare documents.
What happens next?
The head of the personnel department sends a request to the information technology department to finalize the personnel system: you need to create printed forms - a notification and an additional agreement. IT responds that such a revision will cost so many thousand euros, and the costs are not included in the budget, and, among other things, implementation is possible no earlier than in two months.
Sending curses to the IT department, HR officers go to work on weekends, leaving their young children at home. Immortal feat of labor: 3000 documents were created in two days! The name and passport details of each employee were manually added to the template, and so 3000 times!
Will they be able to forgive themselves for such wasted time when they find out that using Merge (Mail Merge)a template in Word with an Excel table containing personal data, it could be done in twenty minutes :)
STEP 1. Create a document template in Word.
The data that will change are highlighted in red: last name, first name, patronymic, number, date of the employment contract, position, division and endings in words respected and received depending on gender.
STEP 2. Create a data source in Excel.
After we set up the Merge, the program will insert data from the source into the template. Each line is a separate document.
STEP 3. Return to the document template that we created in Word. We are going to work with the "Mailings" tab, study it carefully.
Press the button "Select recipients", select the option "Use an existing list" and open the data source (Excel file with information about employees).
In the next window, select the sheet of the file that contains the required data.
STEP 4. Place the cursor in the place of the template text where you want to insert data from the source, press the "Insert merge field" button and select the required field.
This is what my template looks like after all the merge fields are inserted:
STEP 5. Create Rules that change the endings of the words "respected" and "received" depending on the gender of the employee.
We put the cursor after before the end of the word. Press the "Rules" button and select "IF ... THEN ... ELSE" in the drop-down list.
We write down the rule: if the gender is female, then ... otherwise ...
STEP 6. Format the dates.
If you have already had to use Mailings (Mail Merge) before, you have probably come across the fact that the dates transferred from Excel look completely wrong in Word. For example, instead of 19.06.2012, you will most likely see 6/19/2012, which is unlikely to suit you.
There are different ways to solve this problem, let's use the simplest and most convenient one.
Press the Alt + F9 key combination, now we can see the codes of the merge fields.
Inside the code of the Contract_Date field, put a backslash before the closing curly brace, followed by the formatting key:
(MERGEFIELD "Contract_Date" \\ @ "DD.MM.YYYY"
}
@ - date designation, "DD.MM.YYYY" - date format key of the form 19.06.2012
.
You can use any other format. For example, to make the date look like June 19, 2012, use the key \\ @ "DD MMMM YYYY g.".
To exit the code editing mode, press Alt + F9 again.
STEP 7. Press the button "View results" to check what happened.
STEP 8. Editing the list of employees.
By clicking on the "Change recipient list" button, you can exclude individual entries from the list, check for duplicates, set sorting or filters.
Particular attention should be paid to sorting so that you do not have to waste time folding documents after they are printed. It is most convenient to use sorting by the names of employees or by the names of departments.
STEP 8. Finish the merge.
We press the button "Find and merge". If you select the Print Documents option, the documents are sent to the printer immediately. If you need to view documents and make edits before printing, select "Change individual documents".
A separate Word file is generated with the merge results.
Done! Enjoy the saved time!
UPDATE in response to comment alexey_lao
:
With a simple macro, you can save each document as a separate file.
We save our template as a file with the .docm extension (Word file with macro support).
Press the Alt + F8 key combination (call the Macro window).
In the window that opens, set the "Name" of the macro (for example, SaveFiles) and select our file with the template in the "Macros from" drop-down list (I prefer to create macros directly in the file so that they work even when the file is open on another computer). Press the button "Create".
In the place where the cursor is blinking, write the macro code:
Dim DocNum As Integer
For DocNum \u003d 1 To ActiveDocument.MailMerge.DataSource.Reco rdCount
ActiveDocument.MailMerge.DataSource.Acti veRecord \u003d DocNum
With ActiveDocument.MailMerge
.Destination \u003d wdSendToNewDocument
.SuppressBlankLines \u003d True
With .DataSource
.FirstRecord \u003d ActiveDocument.MailMerge.DataSource.Acti veRecord
.LastRecord \u003d ActiveDocument.MailMerge.DataSource.Acti veRecord
End With
.Execute Pause: \u003d False
End With
With ActiveDocument
.SaveAs FileName: \u003d "C: // Test /" & DocNum, FileFormat: \u003d wdFormatXMLDocument
.Close
End With
Replace C: // Test / with the address of the folder where you want to save the documents.
We close the Visial Basic editor window and return to our document with a template.
Press Alt + F8 again, select the SaveFiles macro in the list and press the "Run" button.
While the computer creates and saves each document in turn, you can have a cup of coffee and a cookie. Or go home to sleep if there are more than a couple of hundred documents :)
______________
You can download the files with examples here:
http://hrexcel.ru/download/excel4hr_istochnik_dannyh.xlsx
http://hrexcel.ru/download/excel4hr_shablon.docx
Experiment!
Now we will look at an example of how to implement merge Word data with data Microsoft SQL Server, while this process will be automated by means VBA Access 2003.
Many, probably, already know how to merge Word documents, for example, with an Excel data source or with the same SQL server, but not everyone knows how to automate this process or implement it into some program.
Once I had a task to automate the merging of a certain Word template with data located on a SQL server, while all this had to be implemented in a program developed in Access 2003 (ADP project). And today I will show an example of solving this problem.
Initial data
And first, let's analyze the initial data, i.e. what we have.
So, as a client, as I said, we will have an Access 2003 ADP project. SQL Server 2012 Express will act as a data source for an example. Microsoft Office 2013 ( and Access 2003).
Let's create a test table on the server and fill it with data ( let's say in database Test). To do this, you can run the following SQL statement.
CREATE TABLE dbo.TestTable (ID INT IDENTITY (1,1) NOT NULL, ProductName VARCHAR (50) NOT NULL, Price MONEY NULL, CONSTRAINT PRIMARY KEY CLUSTERED (ID ASC)) GO INSERT INTO dbo.TestTable (ProductName, Price) VALUES ("Computer", 500) GO INSERT INTO dbo.TestTable (ProductName, Price) VALUES ("Monitor", 400) GO INSERT INTO dbo.TestTable (ProductName, Price) VALUES ("Phone", 200) GO INSERT INTO dbo. TestTable (ProductName, Price) VALUES ("Tablet", 300) GO INSERT INTO dbo.TestTable (ProductName, Price) VALUES ("Printer", 250) GO SELECT * FROM TestTable
Create a connection file to the MS SQL Server data source
Now let's create a connection file (ODC) to our data source. This file will act as a kind of " connection file template", Because later we can and will redefine both the database and the SQL query itself.
To create a connection file to the SQL server, let's open Word and create this file in the standard way, i.e. using the functionality " Newsletters». ( By the way, we have already considered an example of creating a connection to a SQL server from Excel in the material - Excel - Connecting and receiving data from a SQL server).
Then, in the data source selection window, press the button “ Create a».
Then we enter the server address and click “ Further».
Then we select the database and the table for connection, I will remind you again, this is just a template, we will override all these parameters, click “ Further».
And in conclusion, we enter a clear name for the connection file, and we can also immediately save it to the directory we need by pressing the button “ Overview", By default it is saved in « C: \\ Users \\ UserName \\ Documents \\ My Data Sources ". Click " Done».
That's it, the file is created, Word can be closed without saving.
Create a Word mail merge template
Now let's prepare a Word template i.e. this is the document into which we will substitute data from the SQL server database.
All preparation comes down to the fact that we need to insert the merge fields where we need it. This is done as follows. The " Insert -\u003e Quick Blocks -\u003e Field».
Looking for a field MERGEFIELD and enter the name of the field that will correspond to the field in the data source ( in my case it is ProductName and Price). Click " OK».
Since I will have practically no text in this test template, and I will display only two fields, you will most likely have a lot of text and many merge fields.
Access 2003 VBA Code to Merge Word Document with MS SQL Server Data Source
All that remains is to write the VBA code that will do the merge. For example, let's add a StartMerge button and a Price field to filter data on the form. Then, in the Visual Basic editor, write a procedure for merging, let's say with the name MergeWord, and in the event handler of the StartMerge button ( button press) insert the code for calling this procedure. The whole code will look like this ( i commented on it). Let me explain right away that the Word template and the ODC file are in the D: \\ Test \\ directory.
"Procedure for starting the merge Private Sub MergeWord (TemplateWord As String, QuerySQL As String)" First parameter - Path to the Word template Second parameter - Database query string On Error GoTo Err1 Dim ConnectString As String, PathOdc As String Dim WordApp As Object Dim WordDoc As Object "ODC file template for data connection PathOdc \u003d" D: \\ Test \\ TestSourceData.odc "If TemplateWord<> "" Then "Create a Word document Set WordDoc \u003d CreateObject (" Word.document ") Set WordDoc \u003d GetObject (TemplateWord) Set WordApp \u003d WordDoc.Parent" Create a connection to a data source (MS SQL Server) "We take some data from the current ADP connection Project ConnectString \u003d "Provider \u003d SQLOLEDB.1; "& _" Integrated Security \u003d SSPI; "& _" Persist Security Info \u003d True; "& _" Initial Catalog \u003d "& CurrentProject.Connection.Properties (" Initial Catalog ") &"; "& _" Data Source \u003d "& CurrentProject.Connection.Properties (" Data Source ") &"; "& _" Use Procedure for Prepare \u003d 1; "& _" Auto Translate \u003d True; "& _" Packet Size \u003d 4096; "& _" Use Encryption for Data \u003d False; "" Set the data source WordDoc.MailMerge.OpenDataSource NAME: \u003d PathOdc, _ Connection: \u003d ConnectString, _ SQLStatement: \u003d QuerySQL "Make Word Visible WordApp.Visible \u003d True WordApp.Activate" Start Merging With WordDoc.MailMerge .Destination \u003d wdSendToNewDocument .SuppressBlankLines \u003d True .Exesecute Pause With "Close the template without saving WordDoc.close (wddonotsavechanges) Set WordDoc \u003d Nothing Set WordApp \u003d Nothing Else MsgBox" No merge template specified ", vbCritical," Error "End If Ex1: Exit Sub Err1: MsgBox Err.Description WordDoc.close (wddonotsavechanges) WordApp.Quit Set WordDoc \u003d Nothing Set WordApp \u003d Nothing Resume Ex1 End Sub Private Sub StartMerge_Click () Dim Filter As String Filter \u003d "" "Condition If Nz (Me.Price," ")<> "" Then Filter \u003d "WHERE Price\u003e \u003d" & Me.Price End If "Call merge procedure Call MergeWord (" D: \\ Test \\ Template.docx "," SELECT * FROM "" TestTable "" "& Filter &" " ) End Sub
We save and check the work.
After clicking on the (StartMerge) button, Word will start, in which all the data is already filled in and there will be as many documents as there are lines in the source.
As you can see, everything works. That's all for me, I hope the material was useful. While!
7 Lecture number 8
Merging documents.
Merge is used when you need to create a set of documents, for example, stickers with addresses or letters on forms that are sent to a large number of customers. Each letter or sticker contains both general and individual information. For example, the letter should contain an appeal to the customer by last name. Individual information for each letter or sticker comes from the data source.
The merge process consists of several general steps.
Setting up the main document. The main document contains text and graphics that are common to all versions of the master document, such as a return address or letterhead greeting.
Connecting a document to a data source. The data source is a file containing information to be inserted into the document, such as the names and addresses of the recipients of the letter.
Refine the list of recipients or items. Microsoft Office Word creates a copy of the main document for each item or entry in the data file.
Add text placeholders (merge fields) to your document When you merge, the merge fields are populated with data from the data file.
Previewing and completing the merge Before printing an entire set of copies of a document, you can preview each copy.
For mail merge use the tab commands Newsletters.
Merging can also be done using the task pane Mergewhich allows you to complete the entire process step by step. To use the task pane, in the group Start merging in the tab Newsletters select item Start mergingand then paragraph Step by step merge wizard.
Setting up the main document
Start Word.
By default, a blank document opens. Keep it open. If you close it, the commands that need to be executed in the next step are no longer available.
In the tab post office in Group Merge select team Start merging.
Click the type of document you want to create.
For example, you can create the following types of documents.
Set of envelopes The return addresses are the same on all envelopes, but the recipient's address is unique in each case. Select item Envelopes and in the tab Envelope parameters dialog box Envelope parameters indicate your preference for envelope size and text formatting.
Set of address stickers Each sticker contains a name and address, which are unique. Select item stickers and in the dialog Sticker options specify the type of sticker you want.
Set of paperwork or e-mails The main content of all letters or messages is the same, but each of them contains information intended for a specific recipient, such as last name, address, or some other information. To create these types of documents, select letters or emails.
Directory or folder Each item displays information of the same type, such as name or description, but is unique for each item. Click catalogto create a document of this type.
Merge resumption
If you need to interrupt the merge work, you can save the main document and resume the merge later. Microsoft Office Word saves data source and field data. If the task pane was used Merge, Word will return to where you want to resume the mail merge.
When you're ready to resume the mail merge, open the document.
Word will ask for confirmation to open the document, which will run the SQL command.
Since this document is connected to a data source, click Yes... The document text appears, along with any inserted fields.
Connecting a document to a data source
To merge data in a master document, you must connect that document to a data source or data file.
Selecting a data file
In the tab post office in Group Merge select team Select recipients.
Follow these steps.
Open a Microsoft Word application document. This document should contain one table. The first row of the table should contain headers, and the remaining rows should contain records to be included in the master document. You can also use a header data source as a data source (Header data source. A document containing a header row (or record) that is used to combine the specified data source with the main mail merge document.).
Refine the list of recipients or items
When connecting to a specific data file, you may not want to migrate the data of all records of the data file to the main document.
To limit the recipient list or use a subset of the data file items, follow these steps.
In the tab post office in Group Merge select team Change list.
In the dialog box Merge destinations
Highlighting individual records This method is most useful when the list is short. Select the checkboxes next to those recipients that should be included in the list, and uncheck the boxes next to those recipients that should be excluded from the list.
If you know that only a few records need to be included in the master document, you can clear the check box in the title bar and then select only the records you want. Likewise, if you want to include most of the list in your document, select the check box in the title bar, and then uncheck the boxes for the entries you do not want to include.
Sorting records Click the column heading of the item you want to sort by. The list will be sorted in ascending alphabetical order (A to Z).
Filtering records This feature is useful if the list contains entries that you do not want to see or include in the master document.
Add text placeholders (merge fields) to your document
Once the main document is connected to the data file, you can enter the text of the document and add text placeholders to indicate where unique data should appear in each copy of the document.
Placeholder text such as an address or greeting are called merge fields. The fields in Word correspond to the selected column headings in the data file.
Data file columns represent categories of data. The fields added to the main document are text placeholders for these categories.
Data file lines are data records. When you perform a mail merge, Word creates a copy of the main document for each record.
When placing a field in the main document, it is assumed that a certain category of data should appear at its location, for example, a name or an address.
Note. When you insert a mail merge field into the main document, the field name is always enclosed in double angle brackets (""). In compound documents, these brackets are not used for emphasis. They only allow you to distinguish fields in the main document from normal text.
What happens in a merge
In a merge, the data in the first line of the data file replaces the fields in the main document, thereby forming the first compound document. The data on the second line of the data file replaces the fields to form a second compound document, and so on.
Examples of working with fields
Any column header can be added as a field from the data file to the main document. This allows some flexibility in drafting letterheads, stickers, e-mails, and other compound documents.
You can combine fields and separate them using punctuation marks. For example, to form the exact address, you need to insert the following fields into the main document:
"First Name Last Name"
"House and street"
"City", "Region", "Zip code"
For common use cases, such as address blocks or welcome strings, Word provides complex fields — a combination of multiple fields. Examples are listed below.
The Address Block field is a combination of several fields including first name, last name, house and street, city and postal code.
The Greeting String field can include one or more name fields, depending on the greeting words used.
The content of each of the complex fields can be customized accordingly. For example, for an address, you can choose a formal name (Mr. Andrey Alfirovich Jr.), and use “For” instead of “Dear” in the address.
Mapping merge fields to a data file
To be sure that Word will find the column in the data file that corresponds to each address or greeting item, you must map the mail merge fields in Word to the columns in the data file.
To match the fields, select Selection of fields in Group Drafting a document and inserting fields in the tab Newsletters.
A dialog box will open Selection of fields.
The elements of the address and greeting are listed on the left. The column headings from the data file are listed on the right.
Word searches for the appropriate column for each item. The illustration shows that Word automatically matches the column Surname data file field Surname... But he could not oppose other elements to each other. Specifically, from this data file, Word did not find a match for the field Name.
In the list on the right, you can select the column of the data file corresponding to the item on the left. Now the illustration shows that the column Name matches the field Name... There is nothing wrong with the fact that for the fields Appeal, Unique identificator and middle name no match was found. A compound document does not need to use every field. If you add a field for which there is no data in the data file, it appears in the master document as an empty text placeholder — usually an empty line or a hidden field.
Entering content and adding fields
In the main document, click where you want to insert the field.
Use a group Drafting a document and inserting fields in the tab Newsletters.
Add any of the following items.
Address block with name, postal address and other information
Select item Address block.
In the dialog box Inserting an address block select the desired address elements and display formats, and then click OK.
Selection of fieldsthen Word was unable to find some of the information it needed for the address block. Click the arrow next to (no match)
Greeting line
Select item Greeting line.
Choose a greeting string format that includes the title, name, and punctuation following the name.
Select the greeting line text for cases where the recipient's first name is not available, for example, when the data source does not contain the recipient's first or last name, but only the company name.
Click the button OK.
If a dialog box appears Selection of fields, then Word was unable to find some of the information it needed for the welcome string. Click the arrow next to (no match), and then highlight the appropriate field in the data source required for the merge.
Separate fields
Information can be inserted from individual fields such as last name, phone number, or sponsorship amount. To quickly add a field from a data file to the main document, click the arrow next to the list Inserting a data field, and then select the field name.
To use more options when inserting individual fields into your document, follow these steps.
In the tab post office in Group Recording and adding fields select team Insert merge field.
In the dialog box Inserting a data field Perform one of the following actions.
To highlight the address fields that will automatically match the data source fields, even if the data source fields have different names, select Address fields.
To highlight fields that always use data directly from a column in the data file, select Database fields.
In the list Fields click the required field.
Click the button Paste, and then Close.
If a dialog box appears Selection of fieldsthen Word was unable to find some of the information it needs to insert the field. Click the arrow next to (no match), and then highlight the appropriate field in the data source required for the merge.
Custom fields from your Outlook contact list
The only way to include custom contact list fields in the main document is to start the mail merge from Outlook. First, you need to configure the display of the contact list using exactly those fields that should be used in the merge. Then the merge can begin. Once all the necessary settings have been set, Word will automatically open and you can complete the mail merge.
Formatting merge results
To format the data contained in the document, select the mail merge field and format it the same way you format any other text. The selection must include double chevron quotes ( « » ) surrounding the field.
Merge preview and completion
After all the fields have been added to the main document, you can preview the preliminary results of the merge. Before actually completing the merge, you can review the master documents and, if necessary, make the appropriate changes.
To view, do one of the following in the group View Results tabs Newsletters :
Click View Results.
Browse each compound document page by page using the buttons Next post and Previous post in Group Viewing Results.
View a specific document using the command Find a recipient.
Completing the merge
Composite documents can be printed or modified individually. This can be done for all documents or for a subset of documents.
Choose whether to edit the entire set of documents, only the copy currently displayed, or a subset of documents identified by the specified record number. Word saves copies that you want to edit as a single file, where they are separated from each other using page breaks.
Saving the main document
Remember that compound documents are saved separately from the main one. It is convenient to keep the main document separate because it can be useful for subsequent merges.
