A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Word » Mailmerge
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

How do I create a data source within an excel document?



 
 
Thread Tools Display Modes
  #1  
Old April 29th, 2010, 07:34 PM posted to microsoft.public.word.mailmerge.fields
Tracy
external usenet poster
 
Posts: 200
Default How do I create a data source within an excel document?

I am trying to do a mail merge with Word and Excel. It tells me to select a
data source, but not how to create one in Excel.
  #2  
Old April 29th, 2010, 08:05 PM posted to microsoft.public.word.mailmerge.fields
Peter Jamieson
external usenet poster
 
Posts: 4,550
Default How do I create a data source within an excel document?

What Word expects in a data source is that it is a rectangular table.
The first row contains the field names (or column names). Subsequent
rows contain the data. e.g. a very simple example:

City Country
London England
New York USA
Berlin Germany

It would be very easy to set that up in Excel. IN Sheet1, you would put
the text "City" (without the quotes) in cell A1, "Country" in B1,
London in A2, England in B2, New York in A3, USA in B3, and so on.

With Excel data sources it's advisable to
a. have your data in the first sheet in the workbook
b. have the same kind of data in every cell in a column - e.g. if you
have a column that mostly has numbers in it, don't try to put a piece of
text in one of the cells.
c. avoid putting Excel formulas in the cells (OK, that rather reduces
the value of Excel, but it can cause problems during merges)
d. ensure that if you have long texts in a column (over 255
characters), that at least one cell in the first 8 cells in that column
has a long text.
e. avoid more than 255 columns - even though Excel 2007 can have more,
none of the mechanisms that Word uses to get data from Excel can see
beyond 255/256 columns.
f. realise that any cell formatting you apply will probably need to be
re-applied in the form of Word field "format switches" because the
default method that Word uses to retrieve Excel data in Word 2002 and
later (OLE DB) tends to discard cell formatting. So if for example you
have amounts in different currencies in the same column in Excel, make
sure you record the currency symbol in a separate column, or it will
probably be lost. If you want to record US Zip codes, phone numbers,
etc., it will almost certainly be worth spending some time looking
through the articles in this group (and I expect Graham may be along
with some good pointers).

Some of the above may not mean a lot right now, but it probably will,
soon enough.

I know Excel is a very popular data source for merges because many
people use Excel as their primary program for maintaining simple tables
of data, but the way Word gets data from Excel can cause problems, and
those problems can seem arbitrary because a lot of decisions are made
depending on what is in the first 8 (data) cells in each column, i.e.
the results you see can vary

Peter Jamieson

http://tips.pjmsn.me.uk

On 29/04/2010 19:34, Tracy wrote:
I am trying to do a mail merge with Word and Excel. It tells me to select a
data source, but not how to create one in Excel.

  #3  
Old April 29th, 2010, 08:14 PM posted to microsoft.public.word.mailmerge.fields
trip_to_tokyo[_3_]
external usenet poster
 
Posts: 932
Default How do I create a data source within an excel document?

EXCEL 2007

Here is a simple example:-

1. In cells A1 to F1 enter the following:

Title
FirstName
LastName
Address1
City
Country

The above are your column headers (these must agree with your WORD
corresponding fields).

2. In A2 to F2 enter the following:-

Mrs.
Michelle
Ambrose
145 Napoleon Avenue
Sanderstead
England

3. Then for additional rows enter the information that you want to.

If the above helps please hit Yes.

Thanks.




"Tracy" wrote:

I am trying to do a mail merge with Word and Excel. It tells me to select a
data source, but not how to create one in Excel.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:21 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.