CODEWITHSUNDEEP
excelvbams-word
Thomas
Thomas

Reputation: 3

VBA Macro to Paste Chart from Excel, into Word, and Format with Text Wrapping

In my excel document I have a chart that I want to copy and paste into a MS-Word document. I want to avoid linking data, embedding workbooks and resizing (Excel has the chart formatted to my desired size). So I came up with/found the following code that almost works:

Sub PasteChart()

Dim wd As Object
Dim ObjDoc As Object
Dim FilePath As String
Dim FileName As String
FilePath = "C:\Users\name\Desktop"
FileName = "Template.docx"


'check if template document is open in Word, otherwise open it
On Error Resume Next
Set wd = GetObject(, "Word.Application")
If wd Is Nothing Then
    Set wd = CreateObject("Word.Application")
    Set ObjDoc = wd.Documents.Open(FilePath & "\" & FileName)
Else
    On Error GoTo notOpen
    Set ObjDoc = wd.Documents(FileName)
    GoTo OpenAlready
notOpen:
    Set ObjDoc = wd.Documents.Open(FilePath & "\" & FileName)
End If
OpenAlready:
On Error GoTo 0

'find Bookmark in template doc
wd.Visible = True
ObjDoc.Bookmarks("LPPU").Select

 'copy chart from Excel
 Sheets("Group Level Graphs").ChartObjects("Chart 1").Chart.ChartArea.Copy

 'insert chart to Bookmark in template doc
 wd.Selection.PasteSpecial Link:=False, DataType:=14, Placement:=0, _
    DisplayAsIcon:=False

 End Sub

The only issue is that the image is pasted as "In Line with Text" but I need it to be "Square with text wrapping". I can't get Word or Excel to record changing the image to "Square with text wrapping".

The PasteSpecial part only does wdFloatOverText or wdInLine for placement and neither of them solve this issue.

I am very new to VBA and have run out of ideas. I am still trying to find a way to format it, maybe using some kind of WITH statement. However I thought I would attempt to reach out for help while I continue google-foo and learning VBA from Youtube.

Using PasteAndFormat Type:=wdChartPicture links the chart to excel. So that didn't work.

Upvotes: 0

Views: 4102

Answers (1)

Rich Michaels
Rich Michaels

Reputation: 1713

Make sure you have a reference to the Word application in your VBE then immediately following a regular paste (wd.Selection.Paste), add these two lines of code:

wd.Selection.MoveStart word.WdUnits.wdCharacter, Count:=-1
wd.Selection.InlineShapes(1).ConvertToShape.WrapFormat.Type = wdWrapSquare

If you want to continue to use the PasteSpecial method you have in your code then replace the code line above "wd.Selection.MoveStart..." to this:

wd.Selection.MoveEnd word.WdUnits.wdCharacter, Count:=1

The reason is a regular paste leaves the active insertion point at the end of the inserted object. But if a PasteSpecial method is used the active insertion point is at the beginning of the object that was pasted. Why? I have no idea! Word VBA never ceases to amaze me. :-)

Upvotes: 0

Related Questions