Paste Excel chart in word, VBA

Question

I am using Windows XP SP 3. I have written code to paste several charts from Excel 2003 to Word 2003.

Dim word As Object
Dim doc As Object
On Error Resume Next

Set word = GetObject(, "word.application") 'gives error 429 if Word is not open
If Err = 429 Then
    Set word = CreateObject("word.application") 'creates a Word application
    Err.Clear
End If

With word
    .Visible = True
    .Documents.Add
End With

Sheets("Data").Select

For i = 1 To 2
    ActiveSheet.ChartObjects(i).Activate
    ActiveChart.ChartArea.Copy
    With word.Selection
        'Paste Chart
        .Range.PasteSpecial
    End With
Next i

I would like to understand, how can I place the charts from Excel 2003 into created word file in different places? E.x. I would like to place 4 charts in the following order: 1st chart is alligned to the left end of the document (not paragraph), 2nd chart is alligned to the left side of the document, the 3d lies below the 1st, same for the 4th.

Thank you for your answers!

UPD: based on the usefull comments, I have outlined the following solution for my problem. Create template file and insert there a bookmark, named insertHere. The make changes by using Excel VBA in this file.

Here is the code for this

Sub macro()
Dim word As Object
On Error Resume Next

Set word = GetObject(, "word.application") 'gives error 429 if Word is not open
If Err = 429 Then
    Set word = CreateObject("word.application") 'creates a Word application
    Err.Clear
End If

Set templateFile = word.documents.Add(Template:="C:\Users\PC\Desktop\Doc4.dot")
Sheets("Data").Select

ActiveSheet.ChartObjects(1).Activate 
ActiveSheet.ChartObject(1).Select
ActiveChart.ChartArea.Copy
With templateFile.Bookmarks
    .Item("insertHere").Range.Paste
End With
End Sub

However, this code doesn't insert the chart. Can you give me a hint why?

Andy G Andy G · Accepted Answer · 2013-07-24T18:03:26

The easiest way is to define Bookmarks in Word for the locations that you wish to paste the charts to. In Word 2003, if I recall correctly, the option is on the Insert menu, Bookmark. Position the cursor firstly, Insert, Bookmark (Ctrl-Shift-F5) give it a name such as bkChart1 (without spaces). Then:

word.ActiveDocument.Bookmarks("bkChart1").Range.PasteAndFormat
' there are other Paste methods, or PasteSpecial

If you prefer to format the document, and the pasted object, using code then you would do well to record a few macros in Word. It won't create perfect code, but it will help you to discover the methods and properties that you need.

Paste Excel chart in word, VBA

1 Answers