Reply
 
Thread Tools Display Modes
  #1   Report Post  
Posted to microsoft.public.word.mailmerge.fields
ArcticWolf ArcticWolf is offline
external usenet poster
 
Posts: 19
Default Mailmerge named ranges from Excel03 to Word03

Hi,

I'm not sure how best to do this, so any advice and best practice would be
appreciated.

I have a monthly sales report in Excel and I want to mailmerge certain parts
of it into a Word doc to produce a written summary. For example: I have
named my profit cell, but when I come to mail merge it - it doesn't appear in
the 'select table' after selecting the mail merge list. It seems that a
named range which is only one cell doesn't pull through?

I'd like to write something like...
"Dear John, your profit/loss for the month of current month is
profit/loss amount....

I can't really put my source data into a table as it's all over the place.
Is there a way I can get this info populated from excel into word please?

TIA,

AW
  #2   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default Mailmerge named ranges from Excel03 to Word03

Mail merge expects a data source to be a table: in the scenario you
describe, you wouldn't be able to use your data as a data source because
in effect, you have a different "data source" for each item of data and
mailmerge only knows how to work with one.

As long as you are only producing /one/ output document, you should be
able to insert LINKS to the relevant data item, and update the links to
reflect the current data. For example, suppose your profit/loss cell is
in a workbook called c:\wbs\pl.xslx, in a sheet called plsheet and you
have named it using the range name plcell

If you select the profit/loss cell in Excel, Edit-Copy, Edit-Paste
Special in Word and choose Paste Link, and Unformatted text, you should
see the content of the cell. If you click Alt-F9 in Word to show the
underlying field code, you should see something like:

{ LINK Excel.Sheet.8 C:\\wbs\\pl.xlsx plsheet!plcell \a \t }

You can insert similar links for the other data items. These links will
in fact update automatically as you modify the cell content in Excel
(depending on various settings). To preserve a snapshot of the data at a
particular time, you would probably need to
a. save your Word document as (say) 2009-11-25-clientX.docx
b. use ctrl-A to select your document and ctrl-shift-F9 to replace all
the field codes by their results
c. save and close the document. (You can leave out step (a) but doing
it means your original document does not lose the field codes).

Whether this is useful depends on whether you are actually only trying
to produce one of these at a time, or whether you are trying to produce
a whole bunch, one for each client, in which case it sounds as if your
data needs to be structured in a way that Word can use. e.g. is there
one sheet per client? Could you create a sheet that referenced the same
cells for each client and use that (somehow or other) as the data source
for a merge?

Peter Jamieson

http://tips.pjmsn.me.uk

On 25/11/2009 11:49, ArcticWolf wrote:
Hi,

I'm not sure how best to do this, so any advice and best practice would be
appreciated.

I have a monthly sales report in Excel and I want to mailmerge certain parts
of it into a Word doc to produce a written summary. For example: I have
named my profit cell, but when I come to mail merge it - it doesn't appear in
the 'select table' after selecting the mail merge list. It seems that a
named range which is only one cell doesn't pull through?

I'd like to write something like...
"Dear John, yourprofit/loss for the month ofcurrent month is
profit/loss amount....

I can't really put my source data into a table as it's all over the place.
Is there a way I can get this info populated from excel into word please?

TIA,

AW

  #3   Report Post  
Posted to microsoft.public.word.mailmerge.fields
ArcticWolf ArcticWolf is offline
external usenet poster
 
Posts: 19
Default Mailmerge named ranges from Excel03 to Word03

Hi Peter,

Many thanks for the quick and detailed response, much appreciated. I'm
using the simple paste-link method and it's working fine with the odd tweak
here and there in my source data.

Thanks again and kind regards,

AW

"Peter Jamieson" wrote:

Mail merge expects a data source to be a table: in the scenario you
describe, you wouldn't be able to use your data as a data source because
in effect, you have a different "data source" for each item of data and
mailmerge only knows how to work with one.

As long as you are only producing /one/ output document, you should be
able to insert LINKS to the relevant data item, and update the links to
reflect the current data. For example, suppose your profit/loss cell is
in a workbook called c:\wbs\pl.xslx, in a sheet called plsheet and you
have named it using the range name plcell

If you select the profit/loss cell in Excel, Edit-Copy, Edit-Paste
Special in Word and choose Paste Link, and Unformatted text, you should
see the content of the cell. If you click Alt-F9 in Word to show the
underlying field code, you should see something like:

{ LINK Excel.Sheet.8 C:\\wbs\\pl.xlsx plsheet!plcell \a \t }

You can insert similar links for the other data items. These links will
in fact update automatically as you modify the cell content in Excel
(depending on various settings). To preserve a snapshot of the data at a
particular time, you would probably need to
a. save your Word document as (say) 2009-11-25-clientX.docx
b. use ctrl-A to select your document and ctrl-shift-F9 to replace all
the field codes by their results
c. save and close the document. (You can leave out step (a) but doing
it means your original document does not lose the field codes).

Whether this is useful depends on whether you are actually only trying
to produce one of these at a time, or whether you are trying to produce
a whole bunch, one for each client, in which case it sounds as if your
data needs to be structured in a way that Word can use. e.g. is there
one sheet per client? Could you create a sheet that referenced the same
cells for each client and use that (somehow or other) as the data source
for a merge?

Peter Jamieson

http://tips.pjmsn.me.uk

On 25/11/2009 11:49, ArcticWolf wrote:
Hi,

I'm not sure how best to do this, so any advice and best practice would be
appreciated.

I have a monthly sales report in Excel and I want to mailmerge certain parts
of it into a Word doc to produce a written summary. For example: I have
named my profit cell, but when I come to mail merge it - it doesn't appear in
the 'select table' after selecting the mail merge list. It seems that a
named range which is only one cell doesn't pull through?

I'd like to write something like...
"Dear John, yourprofit/loss for the month ofcurrent month is
profit/loss amount....

I can't really put my source data into a table as it's all over the place.
Is there a way I can get this info populated from excel into word please?

TIA,

AW

.

Reply
Thread Tools
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Linking Excel named ranges to Word (all 2003) DesM Microsoft Word Help 0 June 29th 09 11:09 AM
Mail Merge Wizard Named Excel Ranges Michael W Mailmerge 1 July 9th 08 10:14 AM
Named Ranges Don't Show Hlp! dee Mailmerge 3 September 24th 07 10:48 AM
Can't link to Excel2007B2TR Spreadsheet Named Ranges from Word2007 Erik Bo Sørensen Microsoft Word Help 2 November 6th 06 12:27 AM
Problem Working with Named Ranges montgomerymouse Microsoft Word Help 1 January 10th 05 12:10 PM


All times are GMT +1. The time now is 04:49 PM.

Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Word"