Copy Excel chart as picture to a bookmarked location in Word through a macro

[Mathieud]

Hi!
I am trying to create an Excel macro to update a chart located in a Word document (as a picture). The existing chart (at a bookmark in Word) is to be deleted and the new chart from the spreadsheet is to be pasted at the same location (as a picture again). The code looks like this:

#Sub Bookmarkchart()

Application.ScreenUpdating = False

Set objWord = CreateObject("Word.Application")
objWord.Visible = True

'Open Word file location on my computer - can be changed
Set WordDoc = objWord.Documents.Open("C:\Users\mdurox\Desktop\Test\Test.docx")

'Finds existing image and delete it
On Error Resume Next
WordDoc.Shapes("Picture1").Delete
On Error GoTo 0

'copy the new chart from Excel
Sheets("Sheet1").Activate
ActiveSheet.ChartObjects("Chart1").Chart.CopyPicture _
Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture

'Finds the bookmark and pastes the new picture
WordDoc.Bookmarks("Chart1bookmark").Range.PasteSpecial Link:=False, DataType:=wdPasteBitmap _
, Placement:=wdFloatOverText, DisplayAsIcon:=False

'Name the new picture as "picture1" so the process can be replicated over and over
Selection.Name = "Picture1"

'Clean-up
WordDoc.Close
Set WordDoc = Nothing
Set objWord = Nothing
Application.ScreenUpdating = True
End Sub#

I keep getting an error 5342 while trying to paste the picture. Would you have any idea how to fix it?

Thanks a lot for your help!

[macropod]

Try:

Please Login or Register  to view this content.

As you'll see, there is no need to manipulate ScreenUpdating, Activate the worksheet, delete anything from the Word document, name the pasted chart, or have Word visible.