Replace charts in a Word document at bookmarked locations

MathieuD

New Member
Joined
Apr 13, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
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!
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,573
Here's some help...
So replace the paste special part with this..
Code:
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

New Member
Joined
Apr 13, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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

Retired Moderator
Joined
Aug 27, 2007
Messages
3,470
.Bookmarks("Chart1bookmark").Exists
should be:
.Bookmarks.Exists("Chart1bookmark")

ORng.Delete is quite unnecessary...
 

Watch MrExcel Video

Forum statistics

Threads
1,113,979
Messages
5,545,316
Members
410,676
Latest member
M0J0jojo
Top