Help with Excel to Word (2013) Macro

Topic

New Reply

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.

Reply | Quote

Replies

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

Reply | Quote