Embedded Excel spreadsheet

Topic

New Reply

I have an embedded Excel 97 (SR2) spreadsheet in Word 97 (SR2). Can anyone please tell me how I can reference the cells in the embedded sheet so that I can extract the values and use them elsewhere in the word doc. Thanks in anticipation.

Reply | Quote

Replies

Hi Gazza:

There may be a better way, but you could give each cell a bookmark name. Then insert a REF field where you want the value to show up.

Hope this helps.

Reply | Quote

The problem being – how do you REF the cells in the embedded worksheet?

Reply | Quote

I suspect you can’t Gazza. Your best chance would be to reference to the original cell in Excel instead.

Reply | Quote

I think I read the question too quickly. I was thinking of an Excel table that had been pasted into Word, in which case it’s pasted as a word table, which can accept a bookmark.

Sorry.

Reply | Quote

Andrew/Phil,

Thanks for your posts Guys – the best that I’d got to was to make links to the cells in Excel inside a corresponding table in Word.

This does what I’m after – automatically updating the Word doc when Excel changes and I can bookmark the links in Word to make the values available elsewhere if I need to.

My only concern is that the links will take too long to update as I’ll need about 50 of them to get the complete set of data out of Excel. I guess I’ll just have to try it and see how it holds up.

Reply | Quote

Hi Gazza:

I’d be curious as to how well it works out for you. Since there’s more than one way to link an Excel cell to Word, (cut & paste, INCLUDETEXT fields, etc.), maybe you could post back.

Reply | Quote

Hi Gazza,

Here’s another way which may accomplish what you want.

What I would do first is create an area in Excel with all of your values in 1 place. Preferably a new sheet but it doesn’t have to be. The key is to have all the values you want in 1 place. This place can just refer to the key cells elsewhere in the workbook by just =location of item (the ordinary way).

Make sure you include a label for each item. So with, say 5 items (for brevity), you’d have across a row (or down a col):
item1 item2 item3 item4 item5
50 30 25 3 600
where the vals are derived by the assignment mentioned above.

Name the above range something like Sending_values.

Now go into Word doc where the info needs to be retrieved.

Go to Tools | Mail Merge…

Under 1. Main Document, you want to Create something. I don’t think it would matter if you chose Form Letter or Catalog since there’s not much diff between the 2 but for this purpose, there would only be 1 record to be merged anyway (the 2nd row of the above excel range).

Under 2. Data Source Get data, choose Open Data Source and navigate to your excel workbook (remember to choose files of type Excel). When you get to it, you will be given a choice of Entire Spreadsheet or any named ranges such as Sending_values. Guess which ones to pick.

You will then be prompted to enter Mail merge fields but Word will have picked up your col headings in row 1 as merge fields. Stick those whereever you want, can even repeat a field more than once. Alternatively, as has been suggested by others (but I don’t think it matters), you can insert the merge field once, bookmark the value, and then x-ref the bookmark wherever else you need it.

Now you are ready to merge (step 3). Just merge to your document and you’re done.

The downside to this vs linking is that you have to remerge each time the spreadsheet changes. But you can skip merge steps 1 and 2 since they won’t change.

What this approach does is avoid having to bring in the excel values as a table. Even if you link to them via the approach others have suggested, I would still put them in 1 place in excel, and then paste special with a link. You can put the paste special in a page at the end of the doc so people may not see the pasted table (don’t print it or hide it). then just use bookmarks as before for cross-ref’g.

Hope this helps.

Fred

Reply | Quote