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 · Accepted Answer · 2013-07-24 18:03:26Z

4

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.

answered Jul 24, 2013 at 18:03

Andy G

19.4k5 gold badges49 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Andy G Over a year ago

It should be ChartObjects(1), not ChartObject. And double-check that the template still includes the Bookmark - it is easy for it to disappear while modifying a template.

mr.M Over a year ago

It works. However, there is a relly weired behavior: when the template file is opened, then the macros inserts everything where bookmarks are placed. If the template files isn't opened, then nothing is happened. I think that the cause of such behavior is in how I create instance of MS Word -

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

Andy G Over a year ago

Turn it off and on again :). Start a new document (manually) in Word using your template, and check that the Bookmarks are in place. I'm not sure currently what might be causing your issue, so it requires a little investigation.

Andy G Over a year ago

You should learn to debug your code. Create a Breakpoint on a line by pressing F9, step-through the code by pressing F8, etc.. And remove On Error Resume Next - get it working without this first - you need to see all errors.

Collectives™ on Stack Overflow

Paste Excel chart in word, VBA

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related