Update word table cells from excel cell values (XP 2003)

Topic

New Reply

I would like to be able to pick up the value in a spreadsheet cell, and have it appear in a table in word. Lots of values are stored in columns in the worksheet, and I use Countif() to organise how many of each I have. The final report must be in a Word document, which I lay out using a table, then have the value of the Countif() cells appear, each in its own table cell. Rather than copy and paste, can I have Word pick up the Excel cell value and put in into the table? Can I also have Word update the document when the spreadsheet values change? many thanks in advance. Frank

Reply | Quote

Replies

You can link the Excel values in your Word document, so that they will be updated automatically.
– Activate the Excel workbook
– Select one of the cells whose value you want to see in the Word document.
– You can also select a contiguous range of cells instead of a single cell.
– Copy the cell(s) to the clipboard.
– Activate the Word document.
– Position the insertion point where you want to place the data.
– Select Edit | Paste Special…
– Click Paste Link.
– Select one of the formats from the list, for example Formatted Text (if you want to preserve the Excel formatting) or Unformatted Text (if you want to format the values in Word).
– Click OK.

Reply | Quote

Perfect Hans, this does exactly what I need.
Many thanks, Frank

Reply | Quote

Perfect Hans, this does exactly what I need.
Many thanks, Frank

This is a further query to my one of last July. I am now trying to change the field code so that it picks up from a workbook in the same folder as the word doc, even if the two files are moved around ie into other folders/computers. I want the pair to be ‘portable’ between users, and thought I could edit the field code to start with ‘..workbookname etc to refer to a file in the same folder. I can’t tell if this worked or not! The name of the files will not change across users, but the data content will. Any help gratefully accepted. Frank

Reply | Quote

See macropod’s [post=”670027″]Relative Paths in Word Fields (All)[/post].

Reply | Quote

This is a further query to my one of last July. I am now trying to change the field code so that it picks up from a workbook in the same folder as the word doc, even if the two files are moved around ie into other folders/computers. I want the pair to be ‘portable’ between users, and thought I could edit the field code to start with ‘..workbookname etc to refer to a file in the same folder. I can’t tell if this worked or not! The name of the files will not change across users, but the data content will. Any help gratefully accepted. Frank

Hi Frank,

To get the link to update to point to whatever the current folder is, you’ll need to add the macro attached to my [post=”670027″]Relative Paths Star Post[/post] to your Word document.

As an aside, if you name the linked range for a given table in Excel, you then then tell Word to use that range for any updates. This can be useful if the number of rows/olumns in the Excel range is liable to change. On the Word side, all you need to do to implement this is to:
. select the linked data
. press Shift-F9 to expose the field code, which will look like { LINK Excel.Sheet.# “C:\Users\ … \Analysis.xls” “Sheet1!R1C1:R5C5” a r }
. change the field code to { LINK Excel.Sheet.# “C:\Users\ … \Analysis.xls” “RangeName” a r } where ‘RangeName’ is your Excel range’s name.
. press F9 to update the field.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Frank,

To get the link to update to point to whatever the current folder is, you’ll need to add the macro attached to my [post=”670027″]Relative Paths Star Post[/post] to your Word document.

As an aside, if you name the linked range for a given table in Excel, you then then tell Word to use that range for any updates. This can be useful if the number of rows/olumns in the Excel range is liable to change. On the Word side, all you need to do to implement this is to:
. select the linked data
. press Shift-F9 to expose the field code, which will look like { LINK Excel.Sheet.# “C:\Users\ … \Analysis.xls” “Sheet1!R1C1:R5C5” a r }
. change the field code to { LINK Excel.Sheet.# “C:\Users\ … \Analysis.xls” “RangeName” a r } where ‘RangeName’ is your Excel range’s name.
. press F9 to update the field.

Thanks guys – stereo replies! I’ll work on it over the weekend.
Frank

Reply | Quote