Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    May 2015
    Posts
    1
    Thanks
    0
    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.
    Code:
    Sub Demo()
    Selection.Paste
    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 13:10. Reason: Added Code Tags

  2. #2
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Willow Grove, Pennsylvania, USA
    Posts
    205
    Thanks
    4
    Thanked 49 Times in 40 Posts
    This is the macro you need. Read the comments for explanations.
    Code:
    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
  •