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 - Edit>Copy, Edit>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 - Edit>Copy, then Edit>Paste Special, Paste - 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. Another way to embed is to Insert>Object>Create
from File>browse for file
- Copy and link - Edit>Copy, then Edit>Paste Special>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. Another way to link to Insert>Object>Create
from File>browse for file and check Link to file checkbox.
See p. 14-17 of Office Integration Chapter of Office 2003 Introductory
Concepts and Techniques, in particular table on p. 17
Exporting Text Files (such as Comma Separated Values and XML) 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.
A more recent alternative is to export into XML format where the names of fields
are used before and after each data item, as shown below. Notice that the csv
format is much more compact than XML due to the repetition of field names in
XML.
<?xml version="1.0" encoding="UTF-8"?>
<dataroot xmlns:od="urn:schemas-microsoft-com:officedata" generated="2005-11-30T14:32:55">
<person>
<first>Bill</first>
<last>Pegram</last>
<state>Virginia</state>
</person>
<person>
<first>Bob</first>
<last>Pegram</last>
<state>Minnesota</state>
</person>
</dataroot>
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 Tools>Letters and Mailings>Mail
Merge. To use the toolbar, select Tools>Letters and Mailings>Show Mail
Merge Toolbar.
Mail Merge Using Wizard
There are 6 steps in using the wizard:
- Select document type - letters, email, envelopes, labels, directory
- Select starting document - use current document, a template, or an existing
document
- Select recipients - existing list (database), Outlook contacts, type a new
list - Most of the time you will want to use an existing list, so with the
radio button "Use an existing list" selected, click on the Browse
link below this radio button to select the existing file containing the recipients.
As noted above, this is typically an Excel spreadsheet or an Access database.
If one selects an Excel spreadsheet, one will be prompted to select a given
sheet. If one selects an Access database, one will be prompted to select an
existing table in the database. By default all that are shown on the sheet
or table are selected, but one is then prompted to select particular members.
[Note: However, rather than having to select certain members at this stage,
if this is an operation that will be repeated at a later time, it seems easier
to use a Make Table query in Access to produce the table of recipients one
wants. In this way, one can use the saved query in Access to generate the
table rather than having to learn a new Word features for this purpose and
possibly having to respecify this feature every time one does the mail merge.]
- Write letter - This typically involves content that is constant across the
recipients and content that is unique to each recipient (entered in the document
as merge codes)
- Preview the output
- Complete the merge
Mail Merge Using the Toolbar
The principal icons you will want to use are the following:
- Main Document Type (step 1 above)
- Open Data Source (step 3 above - this is used to identify the data source
from which the customized information will be drawn)
- Insert Merge Field (this option will be not be available until you have
opened the data source, once you have opened the data source, you will not
see any message or indication you are connected other than some of the icons
will no longer be greyed out and clicking on the insert merge field icon will
display a list of fields in the data source you have chosen) - These are used
to write the letter (step 4 above)
- View Merged Data - After the merge has been completed, this icon functions
as a toggle switch between seeing the merge code and seeing the actual data
inserted by the merge codes for that particular record
- Propogate Labels (only if you are creating labels)
- First Record, Previous Record, Go to Record, Next Record, Last Record -
determine which record's data will be displayed on the screen through the
merge codes
- Merge to New Document (step 6 above)
- Merge to Printer (step 6 above)
- Merge to Email (step 6 above) - You will be prompted to enter a subject
line and to specify a field in the datasource that contains the email address
of the recipient.
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 in 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 Edit>Replace>click on the
Special button at the bottom and select "Section Break" with "Manual
Line Break".
Revised: November 30, 2005. Comments to William Pegram, wpegram@nvcc.edu