Replace charts in a Word document at bookmarked locations

[MathieuD]

Hi there,

I have an Excel spreadsheet and a Word document that work in pair. The spreadsheet is reviewed and updated regularly so the charts there represent the most up-to-date information. On the other hand, the Word document which contains the same charts (previously pasted as pictures), needs to be updated monthly for reporting purposes.

I am trying to set up a macro that will automatically:
1) copy the charts from the Excel sheet
2) Open the Word document
3) Find the relevant bookmarks
4) delete the existing pictures
5) paste the newest version of the charts as pictures.

This is some code I could find but I keep getting an error message that I cannot solve.

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("OMITTED_FOR_PRIVACY\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                          ' this is where I get an error message "Run Time Error 5432 - The specified data is unavailable"

'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

Note that this query is crossed posted but I haven't received any reply since I submitted it (nearly a month ago): Copy Excel chart as picture to a bookmarked location in Word through a macro.

I was wondering if it could be an issue with the clipboard that does not have time to get the picture before trying to paste it in Word... The script copies the picture "Chart1" but appears to be unable to paste it at all (wdPasteMetafilePicture).

Any idea to remove the error message? Or is there a better method to look at this macro?

Thanks!

 

[NdNoviceHlp]

Here's some help...

Issue with Bookmark

Hello, am trying to paste chart & table to word document using excel vba code. I have created few bookmarks in word. but when I run code am getting multiple chart & graphs. May I know what is the issue? Public Sub copypaste_TemplateTable() Dim pvt_tbl As PivotTable Dim appWrd As...

www.mrexcel.com

So replace the paste special part with this..

Dim ORng As Object
With wordDoc
  'make sure bookmark exists
  If .Bookmarks("Chart1bookmark").Exists Then
    Set ORng = .Bookmarks("Chart1bookmark").Range
    ORng.Delete
    ORng.Paste
    .Bookmarks.Add "Chart1bookmark", ORng
  End If
End With

HTH. Dave

 

[MathieuD]

Hi Dave,

That did the trick! Thanks so much! Note that the "If .Bookmarks..." line gave me an error message but since my spreadsheet and word document are very stable (no format change), the bookmarks are not changing and it should not be a issue for me.

All the best!

 

[Macropod]

.Bookmarks("Chart1bookmark").Exists
should be:
.Bookmarks.Exists("Chart1bookmark")

ORng.Delete is quite unnecessary...