Excel tables in word (linked)

Topic

New Reply

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.

Reply | Quote

Replies

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?

Reply | Quote

I can replicate what you’re describing, but I don’t know the solution.

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…

Reply | Quote

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

Reply | Quote

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. :cheers:

BTW: I could NOT see it in Draft mode but it was visible in Print Layout mode.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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:
29553-2011-12-03-10-15-22
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.

Reply | Quote

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?

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.

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).

Reply | Quote

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 …

[*]go to the excel range name
[*]select range name
[*]copy
[*]swap to word
[*]go to the top of the document
[*]search for the range name
[*]go down 1 paragraph
[*]delete the contents of that paragraph
[*]paste as picture
[*]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?

Reply | Quote