Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    sydney, nsw, australia
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Hi Anon,

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

    Paul Edstein
    [MS MVP - Word]

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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.
    Beryl M


  4. #4
    New Lounger
    Join Date
    Dec 2009
    Location
    sydney, nsw, australia
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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,
    Attached Files Attached Files

  5. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    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
    [MS MVP - Word]

  6. #6
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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.
    Beryl M


Posting Permissions

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