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  

Creating a 'mail merge' using data (in a Range)from multiple workb



 
 
Thread Tools Display Modes
  #1  
Old April 23rd, 2010, 12:32 PM posted to microsoft.public.word.mailmerge.fields
Aardvark
external usenet poster
 
Posts: 6
Default Creating a 'mail merge' using data (in a Range)from multiple workb

I have data in a worksheet cell range (say A1:A10 for argument's sake - and
it's NOT a list). This is replicated over numerous workbooks, all with
different names. I want to produce a report (.doc) containing the values from
cells A1:A10 from workbook 'A', then on the next line down in the report, I
want the contents of cells A1:A10 from workbook 'B', then on the next line
down in the report, the contents of cells A1:A10 from workbook 'C', and so
on. This is to be automated somehow (suggestions welcome!!), so a number of
workbooks can be selected, either graphically or progammatically, and the
process will take the data from each workbook in turn, and place it in the
report, as detailed above.
This is a little like a Mail Merge, but the data is not contained in a list
(The rows of data in A1:A10 have no headings), and the data comes from
multiple workbooks instead of just one.
If it isn't possible to do this as described, is it possible to read the
data from the separate workbooks into a list in a single worksheet, and then
do the equivalent of a mail merge into the report?
Maybe there's a piece of 3rd Party software that will do the above???!!!

Regards,
KG
  #2  
Old April 23rd, 2010, 10:47 PM posted to microsoft.public.word.mailmerge.fields
Doug Robbins - Word MVP
external usenet poster
 
Posts: 8,239
Default Creating a 'mail merge' using data (in a Range)from multiple workb

You would need to use visual basic to access each workbook and copy the data
from the range and paste it into the Word document, then close that workbook
and then move onto the next one and repeat the process.

See the article "Control Excel from Word” at:

http://www.word.mvps.org/FAQs/InterD...XLFromWord.htm

Starting with either a list of the workbooks or if all of the workbooks were
in a folder by themselves, it could all be automated.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

"Aardvark" wrote in message
...
I have data in a worksheet cell range (say A1:A10 for argument's sake -
and
it's NOT a list). This is replicated over numerous workbooks, all with
different names. I want to produce a report (.doc) containing the values
from
cells A1:A10 from workbook 'A', then on the next line down in the report,
I
want the contents of cells A1:A10 from workbook 'B', then on the next line
down in the report, the contents of cells A1:A10 from workbook 'C', and
so
on. This is to be automated somehow (suggestions welcome!!), so a number
of
workbooks can be selected, either graphically or progammatically, and the
process will take the data from each workbook in turn, and place it in the
report, as detailed above.
This is a little like a Mail Merge, but the data is not contained in a
list
(The rows of data in A1:A10 have no headings), and the data comes from
multiple workbooks instead of just one.
If it isn't possible to do this as described, is it possible to read the
data from the separate workbooks into a list in a single worksheet, and
then
do the equivalent of a mail merge into the report?
Maybe there's a piece of 3rd Party software that will do the above???!!!

Regards,
KG


  #3  
Old April 24th, 2010, 07:18 AM posted to microsoft.public.word.mailmerge.fields
Peter Jamieson
external usenet poster
 
Posts: 4,550
Default Creating a 'mail merge' using data (in a Range)from multipleworkb

Another possibility, with one nasty problem, would be as follows.

Create a suitable mailmerge data source (could be an Excel sheet, or a
Word document), that contains two columns called (say) wb and ref, a
header row, and one row for each worksheet range that you want to
include. In the first column you put the full pathname of the Workbook
file - you will probably need either to use single forward slash
separators, e.g.
c:/my workbooks/my workbook.xls
or double backslashes, e.g.
c:\\my workbooks\\my workbook.xls

In the second column you need to put the sheet/range name information, e.g.

Sheet1!R1C1:R5C7
or
Sheet3!myrangename

Then, in your Word Mail Merge main document, insert a nested field
something like this (choose the switches that work for you, but if you
choose an image format, bear in mind that Word will try to fit the
entire image on a single page).

{ LINK Excel.Sheet.8 "{ MERGEFIELD wb }" "{ MERGEFIELD ref }" \a \f 4 \r }

where each pair of {} is inserted using ctrl-F9. If you're using Word
2007, you should probably use

{ LINK Excel.Sheet.12 "{ MERGEFIELD wb }" "{ MERGEFIELD ref }" \a \f 4 \r }

And here's the nasty problem. Sooner or later, Word will "unlink" those
{ MERGEFIELD } fields - if not when you preview your merge, then when it
next opens the Mail Merge Main document. So you need to insert this
nested LINK field immediately before you perform the merge. Saving as an
autotext and replacing any remaining non-nested LINK field by the
autotext is one possible way around that. A bit of automation might be
another.

Merge to a new document, then select the entire document using e.g.
ctrl-A, and update all the fields using F9.

Peter Jamieson

http://tips.pjmsn.me.uk

On 23/04/2010 12:32, Aardvark wrote:
I have data in a worksheet cell range (say A1:A10 for argument's sake - and
it's NOT a list). This is replicated over numerous workbooks, all with
different names. I want to produce a report (.doc) containing the values from
cells A1:A10 from workbook 'A', then on the next line down in the report, I
want the contents of cells A1:A10 from workbook 'B', then on the next line
down in the report, the contents of cells A1:A10 from workbook 'C', and so
on. This is to be automated somehow (suggestions welcome!!), so a number of
workbooks can be selected, either graphically or progammatically, and the
process will take the data from each workbook in turn, and place it in the
report, as detailed above.
This is a little like a Mail Merge, but the data is not contained in a list
(The rows of data in A1:A10 have no headings), and the data comes from
multiple workbooks instead of just one.
If it isn't possible to do this as described, is it possible to read the
data from the separate workbooks into a list in a single worksheet, and then
do the equivalent of a mail merge into the report?
Maybe there's a piece of 3rd Party software that will do the above???!!!

Regards,
KG

  #4  
Old April 26th, 2010, 01:00 PM posted to microsoft.public.word.mailmerge.fields
Aardvark
external usenet poster
 
Posts: 6
Default Creating a 'mail merge' using data (in a Range)from multiple w

Thanks for that. I'll give it a try..
Do I replace "Excel.Sheet.8" with the name of my worksheet, or does this bit
actually do the job? ie should I put, say, Test1.xls.Sheet.1, or something
similar, given that the first of my list of workbooks is "Test1.xls", and the
row of data is in sheet1.

Regards,
Kevin

"Peter Jamieson" wrote:

Another possibility, with one nasty problem, would be as follows.

Create a suitable mailmerge data source (could be an Excel sheet, or a
Word document), that contains two columns called (say) wb and ref, a
header row, and one row for each worksheet range that you want to
include. In the first column you put the full pathname of the Workbook
file - you will probably need either to use single forward slash
separators, e.g.
c:/my workbooks/my workbook.xls
or double backslashes, e.g.
c:\\my workbooks\\my workbook.xls

In the second column you need to put the sheet/range name information, e.g.

Sheet1!R1C1:R5C7
or
Sheet3!myrangename

Then, in your Word Mail Merge main document, insert a nested field
something like this (choose the switches that work for you, but if you
choose an image format, bear in mind that Word will try to fit the
entire image on a single page).

{ LINK Excel.Sheet.8 "{ MERGEFIELD wb }" "{ MERGEFIELD ref }" \a \f 4 \r }

where each pair of {} is inserted using ctrl-F9. If you're using Word
2007, you should probably use

{ LINK Excel.Sheet.12 "{ MERGEFIELD wb }" "{ MERGEFIELD ref }" \a \f 4 \r }

And here's the nasty problem. Sooner or later, Word will "unlink" those
{ MERGEFIELD } fields - if not when you preview your merge, then when it
next opens the Mail Merge Main document. So you need to insert this
nested LINK field immediately before you perform the merge. Saving as an
autotext and replacing any remaining non-nested LINK field by the
autotext is one possible way around that. A bit of automation might be
another.

Merge to a new document, then select the entire document using e.g.
ctrl-A, and update all the fields using F9.

Peter Jamieson

http://tips.pjmsn.me.uk

On 23/04/2010 12:32, Aardvark wrote:
I have data in a worksheet cell range (say A1:A10 for argument's sake - and
it's NOT a list). This is replicated over numerous workbooks, all with
different names. I want to produce a report (.doc) containing the values from
cells A1:A10 from workbook 'A', then on the next line down in the report, I
want the contents of cells A1:A10 from workbook 'B', then on the next line
down in the report, the contents of cells A1:A10 from workbook 'C', and so
on. This is to be automated somehow (suggestions welcome!!), so a number of
workbooks can be selected, either graphically or progammatically, and the
process will take the data from each workbook in turn, and place it in the
report, as detailed above.
This is a little like a Mail Merge, but the data is not contained in a list
(The rows of data in A1:A10 have no headings), and the data comes from
multiple workbooks instead of just one.
If it isn't possible to do this as described, is it possible to read the
data from the separate workbooks into a list in a single worksheet, and then
do the equivalent of a mail merge into the report?
Maybe there's a piece of 3rd Party software that will do the above???!!!

Regards,
KG

.

  #5  
Old April 26th, 2010, 04:00 PM posted to microsoft.public.word.mailmerge.fields
Peter Jamieson
external usenet poster
 
Posts: 4,550
Default Creating a 'mail merge' using data (in a Range)from multiplew

Do I replace "Excel.Sheet.8" with the name of my worksheet

No, Excel.Sheet.8 (or Excel.Sheet.12) is merely an identifier that
Windows uses for "an Excel Worksheet object".

A typical LINK field would look like this:

{ LINK Excel.Sheet.8 "c:\\my workbooks\\my workbook.xls"
Sheet1!R1C1:R5C7 \a \f 4 \r }

So the pathname of your /workbook/ is the next thing after Excel.Sheet.8

But what I'm suggesting is that each workbook name comes from a row in a
Mail Merge Data Source, which I envisage as a completely separate fle
from any of the workbooks you already have, and that instead of the
hardcoded

c:\\my workbooks\\my workbook.xls

you insert that pathname using a MERGEFIELD field, e.g.

{ MERGEFIELD wb }


Peter Jamieson

http://tips.pjmsn.me.uk

On 26/04/2010 13:00, Aardvark wrote:
Thanks for that. I'll give it a try..
Do I replace "Excel.Sheet.8" with the name of my worksheet, or does this bit
actually do the job? ie should I put, say, Test1.xls.Sheet.1, or something
similar, given that the first of my list of workbooks is "Test1.xls", and the
row of data is in sheet1.

Regards,
Kevin

"Peter Jamieson" wrote:

Another possibility, with one nasty problem, would be as follows.

Create a suitable mailmerge data source (could be an Excel sheet, or a
Word document), that contains two columns called (say) wb and ref, a
header row, and one row for each worksheet range that you want to
include. In the first column you put the full pathname of the Workbook
file - you will probably need either to use single forward slash
separators, e.g.
c:/my workbooks/my workbook.xls
or double backslashes, e.g.
c:\\my workbooks\\my workbook.xls

In the second column you need to put the sheet/range name information, e.g.

Sheet1!R1C1:R5C7
or
Sheet3!myrangename

Then, in your Word Mail Merge main document, insert a nested field
something like this (choose the switches that work for you, but if you
choose an image format, bear in mind that Word will try to fit the
entire image on a single page).

{ LINK Excel.Sheet.8 "{ MERGEFIELD wb }" "{ MERGEFIELD ref }" \a \f 4 \r }

where each pair of {} is inserted using ctrl-F9. If you're using Word
2007, you should probably use

{ LINK Excel.Sheet.12 "{ MERGEFIELD wb }" "{ MERGEFIELD ref }" \a \f 4 \r }

And here's the nasty problem. Sooner or later, Word will "unlink" those
{ MERGEFIELD } fields - if not when you preview your merge, then when it
next opens the Mail Merge Main document. So you need to insert this
nested LINK field immediately before you perform the merge. Saving as an
autotext and replacing any remaining non-nested LINK field by the
autotext is one possible way around that. A bit of automation might be
another.

Merge to a new document, then select the entire document using e.g.
ctrl-A, and update all the fields using F9.

Peter Jamieson

http://tips.pjmsn.me.uk

On 23/04/2010 12:32, Aardvark wrote:
I have data in a worksheet cell range (say A1:A10 for argument's sake - and
it's NOT a list). This is replicated over numerous workbooks, all with
different names. I want to produce a report (.doc) containing the values from
cells A1:A10 from workbook 'A', then on the next line down in the report, I
want the contents of cells A1:A10 from workbook 'B', then on the next line
down in the report, the contents of cells A1:A10 from workbook 'C', and so
on. This is to be automated somehow (suggestions welcome!!), so a number of
workbooks can be selected, either graphically or progammatically, and the
process will take the data from each workbook in turn, and place it in the
report, as detailed above.
This is a little like a Mail Merge, but the data is not contained in a list
(The rows of data in A1:A10 have no headings), and the data comes from
multiple workbooks instead of just one.
If it isn't possible to do this as described, is it possible to read the
data from the separate workbooks into a list in a single worksheet, and then
do the equivalent of a mail merge into the report?
Maybe there's a piece of 3rd Party software that will do the above???!!!

Regards,
KG

.

  #6  
Old April 27th, 2010, 02:47 PM posted to microsoft.public.word.mailmerge.fields
Aardvark
external usenet poster
 
Posts: 6
Default Creating a 'mail merge' using data (in a Range)from multiple w

Hi, tried this per the instructions, but all I got in the mail merge doc was:
a) The {MERGEFIELD wb} became wb which seems to be an SQL statement:
SELECT * FROM 'Sheet1$', and
b) this got replaced with the actual text strings from the excel sheet
containing them. I managed to get the different rows by using {NEXT RECORD},
but that's as far as I got. I presume that instead, I should have received
the values from the workbook that the range statement was pointing to...

I least we tried!!

"Peter Jamieson" wrote:

Do I replace "Excel.Sheet.8" with the name of my worksheet


No, Excel.Sheet.8 (or Excel.Sheet.12) is merely an identifier that
Windows uses for "an Excel Worksheet object".

A typical LINK field would look like this:

{ LINK Excel.Sheet.8 "c:\\my workbooks\\my workbook.xls"
Sheet1!R1C1:R5C7 \a \f 4 \r }

So the pathname of your /workbook/ is the next thing after Excel.Sheet.8

But what I'm suggesting is that each workbook name comes from a row in a
Mail Merge Data Source, which I envisage as a completely separate fle
from any of the workbooks you already have, and that instead of the
hardcoded

c:\\my workbooks\\my workbook.xls

you insert that pathname using a MERGEFIELD field, e.g.

{ MERGEFIELD wb }


Peter Jamieson

http://tips.pjmsn.me.uk

On 26/04/2010 13:00, Aardvark wrote:
Thanks for that. I'll give it a try..
Do I replace "Excel.Sheet.8" with the name of my worksheet, or does this bit
actually do the job? ie should I put, say, Test1.xls.Sheet.1, or something
similar, given that the first of my list of workbooks is "Test1.xls", and the
row of data is in sheet1.

Regards,
Kevin

"Peter Jamieson" wrote:

Another possibility, with one nasty problem, would be as follows.

Create a suitable mailmerge data source (could be an Excel sheet, or a
Word document), that contains two columns called (say) wb and ref, a
header row, and one row for each worksheet range that you want to
include. In the first column you put the full pathname of the Workbook
file - you will probably need either to use single forward slash
separators, e.g.
c:/my workbooks/my workbook.xls
or double backslashes, e.g.
c:\\my workbooks\\my workbook.xls

In the second column you need to put the sheet/range name information, e.g.

Sheet1!R1C1:R5C7
or
Sheet3!myrangename

Then, in your Word Mail Merge main document, insert a nested field
something like this (choose the switches that work for you, but if you
choose an image format, bear in mind that Word will try to fit the
entire image on a single page).

{ LINK Excel.Sheet.8 "{ MERGEFIELD wb }" "{ MERGEFIELD ref }" \a \f 4 \r }

where each pair of {} is inserted using ctrl-F9. If you're using Word
2007, you should probably use

{ LINK Excel.Sheet.12 "{ MERGEFIELD wb }" "{ MERGEFIELD ref }" \a \f 4 \r }

And here's the nasty problem. Sooner or later, Word will "unlink" those
{ MERGEFIELD } fields - if not when you preview your merge, then when it
next opens the Mail Merge Main document. So you need to insert this
nested LINK field immediately before you perform the merge. Saving as an
autotext and replacing any remaining non-nested LINK field by the
autotext is one possible way around that. A bit of automation might be
another.

Merge to a new document, then select the entire document using e.g.
ctrl-A, and update all the fields using F9.

Peter Jamieson

http://tips.pjmsn.me.uk

On 23/04/2010 12:32, Aardvark wrote:
I have data in a worksheet cell range (say A1:A10 for argument's sake - and
it's NOT a list). This is replicated over numerous workbooks, all with
different names. I want to produce a report (.doc) containing the values from
cells A1:A10 from workbook 'A', then on the next line down in the report, I
want the contents of cells A1:A10 from workbook 'B', then on the next line
down in the report, the contents of cells A1:A10 from workbook 'C', and so
on. This is to be automated somehow (suggestions welcome!!), so a number of
workbooks can be selected, either graphically or progammatically, and the
process will take the data from each workbook in turn, and place it in the
report, as detailed above.
This is a little like a Mail Merge, but the data is not contained in a list
(The rows of data in A1:A10 have no headings), and the data comes from
multiple workbooks instead of just one.
If it isn't possible to do this as described, is it possible to read the
data from the separate workbooks into a list in a single worksheet, and then
do the equivalent of a mail merge into the report?
Maybe there's a piece of 3rd Party software that will do the above???!!!

Regards,
KG
.

.

  #7  
Old April 28th, 2010, 07:25 AM posted to microsoft.public.word.mailmerge.fields
Peter Jamieson
external usenet poster
 
Posts: 4,550
Default Creating a 'mail merge' using data (in a Range)from multiplew

I least we tried!!

:-) I would guess you're not familiar with Mailmerge, which means there
are plenty of opportunities to go wrong here.

{ MERGEFIELD wb } and wb are really just different ways Word has to
display the same thing. Word can also show data from the "active record
in the data source" when you preview.

a) The {MERGEFIELD wb} becamewb which seems to be an SQL
statement:


If you are seeing a SQL Statement in the output, the most likely reason
is that you have one in your data source.

b) this got replaced with the actual text strings from the excel sheet
containing them. I managed to get the different rows by using {NEXT

RECORD},
but that's as far as I got. I presume that instead, I should have

received
the values from the workbook that the range statement was pointing to...


My guess is that you are previewing the data. What you really need to do
is merge to a new document. You should not need the { NEXT } /Next
record field. What you should see is a document containing one LINK
field for each row in your merge data source. When you select and update
those LINK fields, Word should insert the data from each workbook/range
you defined in the data source. You would typically need to use Alt-F9
to see those results.

I least we tried!!


Perhaps worth one more try, but probably not more than that!

Peter Jamieson

http://tips.pjmsn.me.uk

On 27/04/2010 14:47, Aardvark wrote:
Hi, tried this per the instructions, but all I got in the mail merge doc was:
a) The {MERGEFIELD wb} becamewb which seems to be an SQL statement:
SELECT * FROM 'Sheet1$', and
b) this got replaced with the actual text strings from the excel sheet
containing them. I managed to get the different rows by using {NEXT RECORD},
but that's as far as I got. I presume that instead, I should have received
the values from the workbook that the range statement was pointing to...

I least we tried!!

"Peter Jamieson" wrote:

Do I replace "Excel.Sheet.8" with the name of my worksheet


No, Excel.Sheet.8 (or Excel.Sheet.12) is merely an identifier that
Windows uses for "an Excel Worksheet object".

A typical LINK field would look like this:

{ LINK Excel.Sheet.8 "c:\\my workbooks\\my workbook.xls"
Sheet1!R1C1:R5C7 \a \f 4 \r }

So the pathname of your /workbook/ is the next thing after Excel.Sheet.8

But what I'm suggesting is that each workbook name comes from a row in a
Mail Merge Data Source, which I envisage as a completely separate fle
from any of the workbooks you already have, and that instead of the
hardcoded

c:\\my workbooks\\my workbook.xls

you insert that pathname using a MERGEFIELD field, e.g.

{ MERGEFIELD wb }


Peter Jamieson

http://tips.pjmsn.me.uk

On 26/04/2010 13:00, Aardvark wrote:
Thanks for that. I'll give it a try..
Do I replace "Excel.Sheet.8" with the name of my worksheet, or does this bit
actually do the job? ie should I put, say, Test1.xls.Sheet.1, or something
similar, given that the first of my list of workbooks is "Test1.xls", and the
row of data is in sheet1.

Regards,
Kevin

"Peter Jamieson" wrote:

Another possibility, with one nasty problem, would be as follows.

Create a suitable mailmerge data source (could be an Excel sheet, or a
Word document), that contains two columns called (say) wb and ref, a
header row, and one row for each worksheet range that you want to
include. In the first column you put the full pathname of the Workbook
file - you will probably need either to use single forward slash
separators, e.g.
c:/my workbooks/my workbook.xls
or double backslashes, e.g.
c:\\my workbooks\\my workbook.xls

In the second column you need to put the sheet/range name information, e.g.

Sheet1!R1C1:R5C7
or
Sheet3!myrangename

Then, in your Word Mail Merge main document, insert a nested field
something like this (choose the switches that work for you, but if you
choose an image format, bear in mind that Word will try to fit the
entire image on a single page).

{ LINK Excel.Sheet.8 "{ MERGEFIELD wb }" "{ MERGEFIELD ref }" \a \f 4 \r }

where each pair of {} is inserted using ctrl-F9. If you're using Word
2007, you should probably use

{ LINK Excel.Sheet.12 "{ MERGEFIELD wb }" "{ MERGEFIELD ref }" \a \f 4 \r }

And here's the nasty problem. Sooner or later, Word will "unlink" those
{ MERGEFIELD } fields - if not when you preview your merge, then when it
next opens the Mail Merge Main document. So you need to insert this
nested LINK field immediately before you perform the merge. Saving as an
autotext and replacing any remaining non-nested LINK field by the
autotext is one possible way around that. A bit of automation might be
another.

Merge to a new document, then select the entire document using e.g.
ctrl-A, and update all the fields using F9.

Peter Jamieson

http://tips.pjmsn.me.uk

On 23/04/2010 12:32, Aardvark wrote:
I have data in a worksheet cell range (say A1:A10 for argument's sake - and
it's NOT a list). This is replicated over numerous workbooks, all with
different names. I want to produce a report (.doc) containing the values from
cells A1:A10 from workbook 'A', then on the next line down in the report, I
want the contents of cells A1:A10 from workbook 'B', then on the next line
down in the report, the contents of cells A1:A10 from workbook 'C', and so
on. This is to be automated somehow (suggestions welcome!!), so a number of
workbooks can be selected, either graphically or progammatically, and the
process will take the data from each workbook in turn, and place it in the
report, as detailed above.
This is a little like a Mail Merge, but the data is not contained in a list
(The rows of data in A1:A10 have no headings), and the data comes from
multiple workbooks instead of just one.
If it isn't possible to do this as described, is it possible to read the
data from the separate workbooks into a list in a single worksheet, and then
do the equivalent of a mail merge into the report?
Maybe there's a piece of 3rd Party software that will do the above???!!!

Regards,
KG
.

.

 




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:06 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.