Wednesday, February 28, 2007

There and Back Again: Dynamic Linking from Word to Excel

Field codes: Link field - Word - Microsoft Office Online

Dear Absolutely Everyone,

This information is for you! Have you even wanted to construct a Word document with a link to information in an Excel document that would dynamically update every time you updated the Excel document. "Yes," you say, "of course." Well, the answer to this is "Paste Special."

I never realized that Paste Special created a field.

Copy the cell or range from Excel. 'Paste special' into Word. If you choose the link option from the dialog box, it will automatically update whenever you update and save the Excel file. "This," you are saying, "is good stuff." I knew you'd like it.

Of course, a lot of you already knew this, but you are just saying, "Why didn't she ask."

The code for the pasted field is viewable, by the way, if (in Word) you go to Tools/Options/View tab and check Field codes.

When you do, you'll see code like this.
{LINK Excel.Sheet.8 "C:\\Documents and Settings\\bubba\\My Documents\\test.xls" "Sheet1!R1C3" \a \t}

Let's not discuss all this code, but if you are working with something like this, you may want an example to look at.

Betsy

4 comments:

Conversely said...

Let's do discuss that code:

I think I understand the idea behind what this "Paste Special" thing does, but I've never had to use Excel and I've definitely never needed a word document to update an Excel spreadsheet. Anyway, here's my question: Bubba? Is this some kind of codespeak? Or are you referring to some particular person?

brd said...

Um...the bubba part is the name I chose to replace a personal identifier (in case it should somehow jeopardize computer security.) So yes, I'm referring to a particular person, but it is just me.

Here's a grand thing. Perhaps you won't have too much opportunity to create a Word document that pulls dynamic information from an excel file. But, you can use this same concept to have one word document update another. Let's see, how would that be useful? You have created a syllabus for one of your classes. No, you have six different Word documents that have the same course description in them. If you know that course description will change regularly, you could put the description in a separate document and Paste Special it into the other six. Then to change all six documents, you just change the one (call it the master) document. Voila! All six are updated.

Conversely said...

Well now you're talking! I'll have to diddle with this...

cadh 8 said...

I sent this link to someone in my company who was asking me the other day how to do this. I encouraged her to explore the blog site...But I did warn her that it is "wild, wonderful and at times weird". :) I am going to experiment with this when I have time, because it would help with the monthly reports we do.