Excel chart pasted into Word as link is cropped

Topic

New Reply

Recently, we have had a problem when pasting Excel charts into Word documents. Normally this is done using a macro, but the same problem occurs when pasting an individual chart manually within Word.

When pasting unlinked, the full chart appears; when pasting as a link, the chart is cropped at the right.
(Sample attached) [edit: see later post for attached files]

Any ideas why this is happening, or how to fix change the macro so that cropping does not occur? This is the current form of the macro:

Code:

Sub Paste_chart_link_18cm()
'
    Selection.PasteSpecial Link:=True, DataType:=wdPasteOLEObject, Placement:= _
        wdInLine, DisplayAsIcon:=False
    Selection.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend
    Selection.InlineShapes(1).LockAspectRatio = msoTrue
    Selection.InlineShapes(1).Height = CentimetersToPoints(11.8)
        Selection.InlineShapes(1).Width = CentimetersToPoints(18)
End Sub

I tried adding

Code:

Selection.InlineShapes(1).PictureFormat.CropRight = 0#

but that did not help.

Using: Word 2010, Excel 2010, Windows XP

Reply | Quote

Replies

Hi Anon,

Do you have a sanitized copy of the workbook, with the chart, that you can post?

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

The chart is not being cropped, it’s being resized to match the height of the space in one instance and the width in the other; and where it’s the height, the part of the chart that won’t fit on the righthand side is just not showing. Reduce the size some more and the rest appears.

I suspect you need to decide which of the statements in your macro you wish to be true – at the moment you have three statements, any two of which will work together, but not all three; lock ratio = true and both the height and the width measurements.

Unless every chart you paste is going to be EXACTLY the same height:width ratio every time, if you want to keep your ratio locked (which is of course the desirable course) you need to specify EITHER the height OR the width and let XL/Word set the other according to the original ratio – specify both and unless by chance the chart was exactly that shape to begin with, something’s got to give.

At the moment, you lock the ratio and set the height, then the width on this macro; and I suspect on the other you have them the other way around. Therefore, here, with lock ratio on, the software sets the height, with the appropriate width – then changes the width and has to make the height match. So you have a chart that is the right width (but much shorter). If in the other macro the statements are the other way around, it’ll set the width with the appropriate height, then change the height and makes the width match – giving a chart that is the right height for the space, but far too wide.

I imagine you’ll get the result you want if you remove the height line from the macro, letting it set the width (to match the page) and make the height match, because the ratio is locked, but if you want the other way around then go for it.

Reply | Quote

Thanks for your responses.

Macropod, revised sanitised versions of both documents are attached to this post.

BerylM, the chart is cropped. If you look at the right margin, you will see that the right side of the box and the end of the x-axis have been cut off. Resizing will not display them; using the cropping tool will.

I agree that it would be preferable for me to lock the ratio then resize to the required width and let Word resize the height automatically. However when I did this, it did not work as expected, so I added the other dimension in. I probably should have commented out the locked ratio, but I didn’t have time to play around with the resizing too much.

Regards,

Reply | Quote

Hi Anon,

After inserting the Excel file into a Temp folder on my system, then opening the Word document and allowing the link to update, the linked chart re-sized itself so that the right-hand side was no longer cropped.

Of course, there’s no code in the document to test, so there’s nothing to report there. However, you might get better results with your macro if you use:

Code:

Sub Paste_chart_link_16cm()
Dim SngWdth As Single, SngHght As Single, SngScale As Single
SngWdth = CentimetersToPoints(16)
With Selection
  .PasteSpecial Link:=True, DataType:=wdPasteOLEObject, Placement:= _
        wdInLine, DisplayAsIcon:=False
  .MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend
  With .InlineShapes(1)
    SngScale = SngWdth / .Width
    .Height = .Height * SngScale
    .Width = .Width * SngScale
  End With
End With
End Sub

Note that I’ve reduced the chart width by 2cm so it fits better on the page. Again, works fine on my system.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

BerylM, the chart is cropped. If you look at the right margin, you will see that the right side of the box and the end of the x-axis have been cut off. Resizing will not display them; using the cropping tool will.

Well, all I can say is, when I opened your example doc, right-clicked on the ‘cropped’ picture, chose ‘size’ and reduced the percentage, the rest of the picture began to appear.

Reply | Quote