[ArcticWolf]

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

[Peter Jamieson]

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

[ArcticWolf]

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
.