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
.