+ Reply to Thread
Results 1 to 2 of 2

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

Hybrid View

  1. #1
    Registered User
    Join Date
    03-18-2020
    Location
    Melbourne
    MS-Off Ver
    365
    Posts
    1

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

    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!
    Attached Files Attached Files

  2. #2
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

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

    Try:
    Sub BookmarkChart()
    Dim objWord As Object, WordDoc As Object, BkMkRng As Object, StrBkMk As String
    Set objWord = CreateObject("Word.Application")
    objWord.Visible = False
    'Open Word file location on my computer - can be changed
    Set WordDoc = objWord.Documents.Open("C:\Test.docx", False, False, False)
    StrBkMk = "Chart1Bookmark"
    'copy the new chart from Excel
    Sheets("Sheet1").ChartObjects("Chart1").Chart.CopyPicture _
    Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture
    'Finds the bookmark and pastes the new picture
    With WordDoc
      If .Bookmarks.Exists(StrBkMk) Then
        Set BkMkRng = .Bookmarks(StrBkMk).Range
        BkMkRng.Paste
        .Bookmarks.Add StrBkMk, BkMkRng
      End If
      .Close True
    End With
    objWord.Quit
    Set BkMkRng = Nothing: Set WordDoc = Nothing: Set objWord = Nothing
    End Sub
    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.
    Cheers,
    Paul Edstein
    [Fmr MS MVP - Word]

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Copy Excel range a number of sheets as pictures to MS Word, each picture on new Word page.
    By IGTsmith in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-14-2018, 09:23 AM
  2. Macro to copy/paste picture of table in Excel worksheet into a Word document
    By grbears in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-25-2017, 11:25 AM
  3. VBA copy excel as a picture to word - zoom picture
    By michelle 1 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-01-2016, 06:47 AM
  4. How to copy excel chart to microsoft word with Macro button
    By wongth7 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-02-2015, 01:37 AM
  5. Replace un picture/chart in Word from Excel in VBA
    By frankie666 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-01-2013, 03:11 AM
  6. [SOLVED] macro: insert picture based on workbook location, not picture root path.
    By NicksDad in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-03-2012, 07:47 AM
  7. Replies: 1
    Last Post: 05-05-2005, 06:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1