Using Microsoft Office 2007 Applications Together

Excel Spreadsheet in a Word document

There are three different behaviors that can result from putting an Excel spreadsheet into a Word document:

• Copy and paste - Source document becomes part of destination document, an Excel worksheet becomes a Word table. In the Word table, formulas no longer operate; a change in one value doesn't change another.
  
  
• Copy and embed - Copy, then Paste Special, and choose Microsoft Office Excel Worksheet - Source document becomes part of destination document, double clicking object allows you to make changes in the data and formulas will be recalculated in the Word document but not in the Excel spreadsheet.
  
  
• Copy and link - Copy, then Paste Special, click on Link radio button and choose Microsoft Office Excel Worksheet >Paste link - The source document does not become part of the destination document - changes in the source document are reflected in the destination document, one does not double click the destination document to change the data but instead one changes it directly in the source document.

Exporting Text Files (such as Comma Separated Values) as a Data Interchange Format

If you want to send data that is in Excel or Access to someone else, most of the time they will have these programs or have programs that can import Excel or Access files. Another alternative is to export your data into a text file and send this instead and have them import the data into their application. The most common text format for this purpose has been comma-separated-value format (files typically had a csv extension) where each record appears on a new line and field values are separated by commas. Sometimes quotes are used around text values. For example, an Access table with fields first, last, and state and Bill Pegram Virginia as the first record and Bob Pegram Minnesota as the second record would export into csv format as

"first","last","state"
"Bill","Pegram","Virginia"
"Bob","Pegram","Minnesota"

In the exporting process, Access gives one a choice as to whether field names will appear in the first row, and whether one should use double quotes, single quotes, or nothing around text values. An alternative to using a delimited format such as csv is to use fixed width columns.

Mail Merge

Mail merge can be used for generation of form letters, labels, or emails where each letter, label, or email typically contains some information that is common to all the letters, labels, or emails, and other customized information that varies among the letters, etc., and is taken from a database. To do a mail merge in Word, one creates a document consisting of content and merge fields. In the mail merge, the merge fields are replaced by information from a single record in a database such as Access or Excel. Mail merge can also be used for displaying Access data rather than using Access reports.

One can do a mail merge in Word by using a wizard or through use of the mail merge toolbar. To use the mail merge wizard, select Mailings>Start Mail Merge>Step by Step Mail Merge Wizard. To use the toolbar, select Mailings>Start Mail Merge. The detailed instructions for the latter are shown below.

Mail Merge

In Word do the following:

1. Choose Mailings>Start Mail Merge>and then choose what kind of document you want to create. We will initially assume a letter.
2. Click on Select Recipients>Use Existing List>and then browse for the file (usually, but not limited to Excel, Access, or csv file) containing the information about the recipients. If you select an Excel spreadsheet, you will then be asked to click on the sheet you want; if you select an Access database, you will then be asked to click on the table or query you want.
3. Write letter - whenever you want to insert content that is retrieved from the database (Excel, Access, etc), click on the "Insert Merge Field" icon and select the field you want. Then select close and continue.
4. The merge fields will be denoted by the name of the field surrounded by << and >>. To see the actual values, click on the Preview Results icon. Immediately to the right of this button are the controls whereby one can go forward and back within the database.
5. Click on the "Finish and Merge" icon and select the appropriate choice. If you click on the "Send email message" a dialog box will appear. If you have a field in the database named email, it will be used for the TO: line of the email. The subject line of the email is typed in, and thus will be the same for all emails sent at this point. It is often quicker to only generate a few emails and check the results before generating all the emails. The emails are put in the Outbox of the associated email application. If you are using Outlook, it is helpful to set the default mail conditions such that emails are only sent when one clicks Send/Receive, otherwise the emails generated to test the results would likely be sent out.

The Address Block and Greeting Line icons are shortcuts for creating the address block and greeting line in a letter; you can achieve more customized results using individual merge fields.

Printing from a Mail Merge

If one uses mail merge to print, each record used is put into a different section of the resulting document. Each section will start printing on a new page. Where this is exactly what one wants for letters, in other uses of mail merge (e.g. using mail merge to display the contents of a database as an alternative to using reports), one doesn't want each record to start on a new page. One can do a search and replace for these section breaks and replace them with something else, e.g. a line break. To do this, select Home>Replace>click on the More button if it is showing>click on the Special button at the bottom and select "Section Break". Then click in the Replace textbox and then again click on the Special button at the bottm and select "Line Break".

Revised: December 3, 2008. Comments to William Pegram, wpegram@nvcc.edu