Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    London, Gtr London, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.
    Attached Images Attached Images
    • File Type: png x.png (15.2 KB, 0 views)

  3. #3
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    London, Gtr London, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

  4. #4
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    London, Gtr London, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='frankwalsh1962' post='720647' date='09-Jul-2008 04:19']Perfect Hans, this does exactly what I need.
    Many thanks, Frank[/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

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    [quote name='frankwalsh1962' post='773239' date='01-May-2009 21:05']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[/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 Relative Paths Star 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
    [MS MVP - Word]

  7. #7
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    London, Gtr London, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='macropod' post='773247' date='01-May-2009 12:54']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 Relative Paths Star 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.[/quote]

    Thanks guys - stereo replies! I'll work on it over the weekend.
    Frank

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •