Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    May 2015
    Thanked 0 Times in 0 Posts

    Help with Excel to Word (2013) Macro

    After many, many hours of searching the web I cant quite get a macro to work. I have very little programming experience so any help would be appreciated.

    The macro i want to create will take an excel chart, that has been copied to the clipboard and paste it into word. Specifically (and this is what I cant get to work right) I need to pastespecial as a GIF (not as an active, editable chart which is all I can get it to do.) Then that chart needs to be set to height 4" and width 5.51" with a wrap text, In front of text. I have tried this by just recording my key strokes, but when you paste in the chart it will not select it in record mode. So then I turned to coding it and I found this on another thread here, which almost seems to get it to work.
    Sub Demo()
    Application.ScreenUpdating = False
    Selection.ShapeRange.LockAspectRatio = msoFalse
    Selection.ShapeRange.Height = 4
    Selection.ShapeRange.Width = 5.51
    Selection.ShapeRange.WrapFormat.Type = wdWrapBehind
    Application.ScreenUpdating = True
    End Sub
    The problem is that it copies the chart in as an active, editable chart. I need it to be a picture so it matches the formatting and style I created in excel.Now, I tried changing the "Selection.Paste" to a paste special using some other code, but it broke and gave me an error. Sometimes the WrapBehind will break too. With no code experience its hard to troubleshoot. I I repeat this process hundreds of times per report I do, so creating a macro would save me, and my team at work TONS of time. Any help at all would be amazing.

    TL;DR I need a macro to take a chart from excel copied on the clipboard, paste special as GIF, format it to size 4 height, 5.51 width and wrap In Front of text.
    Last edited by RetiredGeek; 2015-05-22 at 12:10. Reason: Added Code Tags

  2. #2
    3 Star Lounger
    Join Date
    Feb 2001
    Willow Grove, Pennsylvania, USA
    Thanked 49 Times in 40 Posts
    This is the macro you need. Read the comments for explanations.
    Sub Demo()
    Dim optOldWrapType As WdWrapTypeMerged
    'save the existing option setting
    optOldWrapType = Options.PictureWrapType
    'change to the Behind Text setting for this macro
    Options.PictureWrapType = wdWrapMergeBehind
    Application.ScreenUpdating = False
    'paste special as a bitmap (you can't specify GIF or JPG)
    Selection.PasteSpecial DataType:=wdPasteBitmap
    Selection.ShapeRange.LockAspectRatio = msoFalse
    'resize -- the values must be in Points, so convert inches
    Selection.ShapeRange.Height = InchesToPoints(4)
    Selection.ShapeRange.Width = InchesToPoints(5.51)
    Application.ScreenUpdating = True
    'restore the user's original option setting
    Options.PictureWrapType = optOldWrapType
    End Sub

Tags for this Thread

Posting Permissions

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