Using Excel Data in Word Dynamically

[Sam Elowitch]

One thing that confuses me about using Word and Excel together is this: I
want to control my typography and layout of a document with Word b/c that
what Word is good at; however, I simultaneously want to manipulate my data
in Excel, and have those changes and recalculations to be reflected
automatically in the companion Word document.

So, is there any easy way to do this? Can you insert a cross-reference to an
Excel cell in a Word table?

-Sam

 

[Sam Elowitch]

Oops. I forgot to say I'm using Word 2004. Saw-we vedy much.

 

[Sam Elowitch]

Oh, wait a minute -- is this supposed to be done via Data Merge? The
lightbuld is starting to flicker on here....

-Sam

 

[Jim Gordon MVP]

Hi Sam,

There are several ways...

One way is to build the Excel workbook right inside of Word. To
accomplish this use Word's Insert menu. Insert > Object > Microsoft
Excel Worksheet.

Another way is to embed an existing Excel Workbook. Insert > Object >
then click the From File button and navigate to the Excel file.

There are other ways, too, if these are not enough.

-Jim

 

[Sam Elowitch]

Hi, Jim.

How about linking to a particular cell of an Excel worksheet from Word? Like
"put the contents of cell D34 in document mydata.xls here"?

-Sam

 

[Sam Elowitch]

Also, most importantly -- when I alter the Excel file, I want the
appropriate references in the Word document to change as well, hence the
word "dynamically."

-Sam

 

[Jim Gordon MVP]

Sam,

That is indeed a tricky proposition.

I have a proposal for you.

First, open Excel to a blank workbook. From the Tools menu choose
Customize > Menus and Toolbars. Click the Commands tab. In the right
side of the dialog box look for the Camera command and drag its picture
to a toolbar then let go. Then click OK to close the Customize dialog box.

Try this out to see if it will work for you:

With an empty Excel Workbook fill in a few cells with data and then move
the cursor down a couple rows.

Now we'll do the opposite of what I suggested previous. From Excel's
Insert menu choose Insert > Object > Microsoft Word Document (you can
make a new one or choose an existing one From File).

What we have now is a live Word document living on an Excel worksheet.

Select a cell or range of cells that contain data. Then click the camera
command button on your toolbar. Then click onto a blank area of the
worksheet. A picture of the cell range you selected will appear. The
data in that picture is linked live to the source range. If you change
any of the source data, it is changed instantly in the picture. The
picture is floating and can be dragged on top of the embedded Word
document. In this way you can give the appearance that the cell is part
of the word document, but it really is floating on top.

Will this work?

-Jim

--
Jim Gordon
Mac MVP
MVP FAQ
<http://mvp.support.microsoft.com/default.aspx?scid=fh;EN-US;mvpfaqs>

 

[Sam Elowitch]

Sam,

That is indeed a tricky proposition.

Seems like a pretty basic feature to me, but okay.

I have a proposal for you.

First, open Excel to a blank workbook. From the Tools menu choose
Customize > Menus and Toolbars. Click the Commands tab. In the right
side of the dialog box look for the Camera command and drag its picture
to a toolbar then let go. Then click OK to close the Customize dialog box.

Try this out to see if it will work for you:

With an empty Excel Workbook fill in a few cells with data and then move
the cursor down a couple rows.

Now we'll do the opposite of what I suggested previous. From Excel's
Insert menu choose Insert > Object > Microsoft Word Document (you can
make a new one or choose an existing one From File).

What we have now is a live Word document living on an Excel worksheet.

Select a cell or range of cells that contain data. Then click the camera
command button on your toolbar. Then click onto a blank area of the
worksheet. A picture of the cell range you selected will appear. The
data in that picture is linked live to the source range. If you change
any of the source data, it is changed instantly in the picture. The
picture is floating and can be dragged on top of the embedded Word
document. In this way you can give the appearance that the cell is part
of the word document, but it really is floating on top.

Will this work?

Yeah, but it's clunky. Not what I really want. I feel as though it should be
possible to do this instead as a data merge with an .xls file as the data
source?

-Sam

 

[Jim Gordon MVP]

Hi Sam,

The thing is, that to solve the general case of bringing data into Word,
it's probably means a solution needs to work for lots of rows as well as
for just one cell.

Have you considered a macro that runs when Word opens that opens the
Excel file, copies the cell or cell range as a picture, then pastes the
picture into the desired place in your Word document?

I can't think of a simple, non-clunky way to accomplish the task you
desire.

-Jim

 

[Sam Elowitch]

Bah. That sucks. You can do a mail merge for stupid mailing labels, but not
a data merge from Excel? What kind of nonsense is that? Arrrgh.

I don't want Excel graphs as graphics, for Pete's sake. I want their CONTENT
only -- let Word determine the appearance.

This is so basic and so obvious that I'm astounded that this feature has
never been conceived of in Redmond.

-Sam

 

[Paul Berkowitz]

Of course you can do a data merge from Excel. Check out the Data Merge
Manager. (Catalog). You can then Convert Text to Table. But you're asking
for dynamic linking _after_ the merge. I don't think that's possible - why
do you expect it has to be? You can also Insert--> File, and again Convert
Text to Table. But again you've got an independent document in Word.

I believe you _could_ hyperlink an individual Table cell in Word to a
worksheet cell in Excel, or vice versa. Check out Hyperlinks in each app.
The Help has a fair bit of information on it. To do something like that for
every cell in the table (or worksheet) I imagine you'd have to do it by
macro or script if it's not be be very tedious. But you could hyperlink
every cell in the Excel worksheet to a cell in a Word table by macro or
script, I'm quite sure.



--
Paul Berkowitz
MVP MacOffice
Entourage FAQ Page: <http://www.entourage.mvps.org/faq/index.html>
AppleScripts for Entourage: <http://macscripter.net/scriptbuilders/>

Please "Reply To Newsgroup" to reply to this message. Emails will be
ignored.

PLEASE always state which version of Microsoft Office you are using -
**2004**, X or 2001. It's often impossible to answer your questions
otherwise.

 

[Jim Gordon MVP]

Hi Sam,

You certainly can use Excel as a data source for a data merge.

You may be able to use a field code in Word to bring the data you seek
from Excel into Word. Check the help topic titled "Field codes: MergeRec
field." You would still need to update field codes to get the current
data from Excel. It would not be completely dynamic.

Another field code that might work for you is listed in this Word help
topic; "Field Codes: Database field." This field code requires
knowledge of ODBC, a little Structured Query Language (SQL) and the
presence of an Excel ODBC driver on your Mac.

-Jim

--
Jim Gordon
Mac MVP
MVP FAQ
<http://mvp.support.microsoft.com/default.aspx?scid=fh;EN-US;mvpfaqs>

 

[Sam Elowitch]

It doesn't let me do that when I select Open Data Source from the Data Merge
Manager. "Word could not merge the main document with the data source
because the data records were empty or no data records matched your query
options." As usual, this error message is a lie, since my workbook is not
empty and I have established no "query options." That's Microsoft for ya!

 

[Jim Gordon MVP]

Hi Sam,

In order for a data merge to work, the data on your worksheet needs to
be arranged in rows and columns. The first row is the column (aka field
or header) names that will appear in the data merge manager.
Subsequent rows should be the corresponding data. The first empty row
signifies the end of the data set. This is fairly standard for all data
tables regardless of the manufacturer of the program.

-Jim

--
Jim Gordon
Mac MVP
MVP FAQ
<http://mvp.support.microsoft.com/default.aspx?scid=fh;EN-US;mvpfaqs>

 

[Sam Elowitch]

I believe you _could_ hyperlink an individual Table cell in Word to a
worksheet cell in Excel, or vice versa. Check out Hyperlinks in each app.
The Help has a fair bit of information on it. To do something like that for
every cell in the table (or worksheet) I imagine you'd have to do it by
macro or script if it's not be be very tedious. But you could hyperlink
every cell in the Excel worksheet to a cell in a Word table by macro or
script, I'm quite sure.

This sounds like exactly what I'm looking for. What's the correct procedure?
When I try Insert --> Hyperlink in Word and select my Excel file, it gives
an error message saying that "Word cannot parse this file type" -- what am I
doing wrong?