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  

Field code to pick up Excel sheet



 
 
Thread Tools Display Modes
  #1  
Old June 1st, 2004, 07:21 PM
Lyndie
external usenet poster
 
Posts: n/a
Default Field code to pick up Excel sheet

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
  #2  
Old June 2nd, 2004, 11:57 AM
macropod
external usenet poster
 
Posts: n/a
Default Field code to pick up Excel sheet

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


 




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 01:10 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.