Results 1 to 8 of 8
  1. #1
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel tables in word (linked)

    I have some quarterly (and next year, monthly) reports that are fairly static (sections, words, types of tables, etc) while the actual numbers quoted in the body of the report and shown in the tables change. In previous versions I was able to cut nicely formatted tables from excel and 'paste special / paste link / as picture' into word. The table would show in draft and print layout view (including the contents) as a picture and I could resize it if needed.

    We recently moved to excel and word 2010 and the above functionality has been lost. I can not see the contents of the linked table in draft or print layout - I can if I print preview the document. Further, I cannot change the size of the linked picture. I am pretty sure that it doesn't actually come in as a picture now because the 'Picture Tools' ribbon doesn't appear when I click on the table.

    If I paste it without the link, then it does present as a picture and I can resize it.

    Questions:
    • Is this a new feature from MS that is designed to help excel and word word together?
    • Am I totally nuts and I am just doing it wrong?
    • How can I get my linked tables that I can resize (and edit as a picture) back?

    At the end of the day, it doesn't need to be a linked picture - I just want the ability to change the numbers in the underlying excel file and have those automatically updated in the word doc.
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

  2. #2
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I've been reading other posts in this forum and quite a few of them talk about macros. Re the above, I could see a macro solution being possible ...

    • macro in excel to highlight range name and export it as a jpeg (or similar)
    • macro in excel to import the jpeg

    One question that I have always had with macros in word is how do I control where actions are done. In excel I can just say 'activecell' or range("A1") ... but how do I do the same sort of thing in word?
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    I can replicate what you're describing, but I don't know the solution.

    Quote Originally Posted by Ruff_Hi View Post
    One question that I have always had with macros in word is how do I control where actions are done. In excel I can just say 'activecell' or range("A1") ... but how do I do the same sort of thing in word?
    Word also has Ranges. The most obvious ones are the body content and each of the headers and footers. The selection also can be addressed as a range, each paragraph is a range, the contents of a bookmark (Word's equivalent of a named range) are a range, and so on. But I'm not sure that will help in this case...

  4. #4
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts
    Tim,

    When I test this on 2010 (on Windows 7), it works fine - the linked picture is visible in all views, and is resizeable. The only thing that appears different from earlier versions is that you can't edit it as a picture. Not sure why it would work differently for us - what version of Windows are you running? Do you have a printer installed, and with up to date drivers?

    With regard to a macro to export as a jpg, couldn't you do the same manually by copying the range in Excel and then pasting as a picture in Word?

    Gary

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Tim,

    I was able to use the Paste, Paste Special...,Paste link:, Picture (Windows Metafile) selection to paste an Excel object into Word 2010 and then center it, wrap text {by right-clicking and selecting Format Object}, and resize it {note: when you select the object there is a little handle in the bottom right corner do to this with}. I hope this helps.

    BTW: I could NOT see it in Draft mode but it was visible in Print Layout mode.
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Canada
    Posts
    122
    Thanks
    3
    Thanked 20 Times in 18 Posts
    The right-click Paste in Word 2010 offers a number of context-sensitive options for pasting from Excel. Copy the range you want in Excel, then right-click where you want it in Word and choose one of the six options presented:

    1. Keep Source Formatting (K) to use the formatting from your Excel sheet but the content is no longer linked
    2. Use Destination Styles (S) to use the current Word styles to format the pasted content but the content is no longer linked
    3. Link & Keep Source Formatting (F) to use the formatting from your Excel sheet and set up a link to the spreadsheet content
    4. Link & Use Destination Styles (L) to use the current Word styles and set up a link to the spreadsheet content
    5. Picture (U) to insert the Excel range as a picture (it can be resized, but is no longer linked)
    6. Keep Text Only (A) just keeps the text (i.e. cells separated by tabs and rows by paragraph marks); not formatting and no link

    The flyout looks like this:
    2011-12-03 10-15-22.jpg
    Options 2 & 3 insert a LINK field code; use Alt-F9 in Word to toggle between the result and field code views. If you have a regular requirement, consider setting up your Word styles to match the Word document formatting and use option #2 to reflect the current values in the linked Excel sheet automatically for you.

    I'd also recommend setting the Word Option to always display field shading (File | Options, Advanced, Show document control). This will show the linked content with a light gray shading overlay so you can see that it is the result of a field code. The gray does not print, but having it visible helps differentiate between typed and calculated content.

  7. #7
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Gary Frieder View Post
    When I test this on 2010 (on Windows 7), it works fine - the linked picture is visible in all views, and is resizeable.
    I am using win XP - maybe that is one of the issues. The other thing is that my Word / Excel 2010 is still set up to use 2003 as the default format. I did try all combinations without much success.

    Does it come through as a field?
    With regard to a macro to export as a jpg, couldn't you do the same manually by copying the range in Excel and then pasting as a picture in Word?
    I guess I could do that - can excel vba 'control' word? If so, do you have an example that I could cheat from?
    Quote Originally Posted by RetiredGeek View Post
    I was able to use the Paste, Paste Special...,Paste link:, Picture (Windows Metafile) selection to paste an Excel object into Word 2010 and then center it, wrap text {by right-clicking and selecting Format Object}, and resize it {note: when you select the object there is a little handle in the bottom right corner do to this with}.
    This is exactly what I have been doing and did repeat.
    Quote Originally Posted by EricFletcher View Post
    Options 2 & 3 insert a LINK field code; use Alt-F9 in Word to toggle between the result and field code views. If you have a regular requirement, consider setting up your Word styles to match the Word document formatting and use option #2 to reflect the current values in the linked Excel sheet automatically for you.
    I have a special style that I created ('Graphic') that is centered, no fixed height, etc that displays the pictures quite nicely.
    I'd also recommend setting the Word Option to always display field shading (File | Options, Advanced, Show document control).
    I totally do this - also show bookmarks, etc. If you don't do this and you update the bookmarks, you end up moving the new item outside the boundaries (I often bookmark the date of the report and then link my header to that bookmark).
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

  8. #8
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I was thinking about this more while I was walking my dog ... I have the tables in excel with range names ... I could set up a style (red, do not print) in word that held the corresponding excel range name that is one paragraph about the graphic in question ... then run an excel macro that does something like this ...

    1. go to the excel range name
    2. select range name
    3. copy
    4. swap to word
    5. go to the top of the document
    6. search for the range name
    7. go down 1 paragraph
    8. delete the contents of that paragraph
    9. paste as picture
    10. swap back to excel

    If I put that in a subroutine and feed it the range name, excel doc name, word doc name then I could call it multiple times with the appropriate range names.

    I've had plenty of experience coding in excel vba so that wouldn't be a problem. The word side if things ... not so much. Can someone with word vba experience tell me if #5 thru #9 is possible? And is it possible to jump back and forth between excel and word?
    Last edited by Ruff_Hi; 2011-12-03 at 23:26.
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

Posting Permissions

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