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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|