[Peter Jamieson]

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
.

.