Hi Lyndie,
With a bit of effort, this can be done. The solution below depends on all of
the files being in the same folder, but could also be made to work with
files in different folders.
To start with open both your Word document and one of the 'source' Excel
workbooks. Next, copy the required range from that workbook and paste it
into Word using Edit|Paste Special and choosing both the 'paste link' option
and the data format you require. There are various formats to choose from,
and you might have to redo the copy & paste afresh with each one to find out
what best suits your needs.
Then, select the pasted data in Word and press Shift-F9. This will expose
the field code, which will look something like:
{ LINK Excel.Sheet.8 "C:\\My Documents\\Excel\\30328765.xls" "Sheet1!R1C1"
\a \t }
Delete the '30328765' and, leaving no spaces between the '\\' and the
'.xls', press Ctrl-F9 to insert a new field, thus:
{ LINK Excel.Sheet.8 "C:\\My Documents\\Excel\\{ }.xls" "Sheet1!R1C1" \a
\t }
Within the new field's braces, type 'Excel', so that you get
{ LINK Excel.Sheet.8 "C:\\My Documents\\Excel\\{Excel}.xls" "Sheet1!R1C1" \a
\t }
Now go to the position immediately before the first field brace and type
Ctrl-F9 again to insert another field, so that you get:
{ }{ LINK Excel.Sheet.8 "C:\\My Documents\\Excel\\{Excel}.xls" "Sheet1!R1C1"
\a \t }
Within these field braces type 'ASK Excel "Which Excel file?"', so that you
get:
{ASK Excel "Which Excel file?"}{ LINK Excel.Sheet.8 "C:\\My
Documents\\Excel\\{Excel}.xls" "Sheet1!R1C1" \a \t }
Nearly done. Now, select both fields and press Ctrl-F9 again. This puts a
pair of filed braces around both fields joining them together, thus:
{ {ASK Excel "Which Excel file?"}{ LINK Excel.Sheet.8 "C:\\My
Documents\\Excel\\{Excel}.xls" "Sheet1!R1C1" \a \t } }
Now, type 'QUOTE' between the first two field braces, thus:
{QUOTE {ASK Excel "Which Excel file?"}{ LINK Excel.Sheet.8 "C:\\My
Documents\\Excel\\{Excel}.xls" "Sheet1!R1C1" \a \t } }
Finally, press F9 to update the field. You'll be prompted to insert the
Excel file's name (eg 30328765 or 3032876). If you do, you'll get the data
from the corresponding file.
Of course, you'll only want to type the Excel file's name into your document
once, and perhaps have it appear elsewhere in the document. Now that you've
done the above, you can achieve that too, simply by typing Ctrl-F9 to create
a field into which you'd type 'Excel', thus:
{Excel}
and pressing F9 to update. This works because the ASK field that you created
inserts a bookmark in your document named 'Excel', which a REF field such as
{Excel} can retrieve. Copy the field and paste it wherever you want the
Excel file's name to appear. To update all the fields in one swoop press
Ctrl-A then F9, or simply make sure the 'update fields' option is checked
under Tools|options|Print and print the document.
Cheers
"Lyndie" wrote in message
...
I have a challenge...
I have an 8 page "Certificate" that will only need two
pieces of variable information. First is the serial
number (i.e. 30328765). There are about 15 of these
serial numbers that could be typed in. Second, depending
on the serial number that is typed in, I need to pick up
data from a similarly-named worksheet in an Excel file.
For example, if I type "30328765" into the Word document,
I also want the data table on an Excel worksheet
named "3032876" to be included in the Word document.
Any help you could send my way is greatly appreciated.
Lyndie
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (
http://www.grisoft.com).
Version: 6.0.684 / Virus Database: 446 - Release Date: 13/05/2004