copy from xls document into word using vba

Question

I'm trying to make a code in which to copy charts from a xls file into a word document using the PasteSpecial property (picture(enhanced metafile). I would like to change the existing charts of the document to new ones. So, I thought that using bookmarks for the existing charts would be OK. I'm using OFFICE 2007.

I've written the following code:

        Dim YMApp As Word.Application
        Dim YMDoc As Word.Document
        Dim B as Bookmark
        paaath = "D:\"
        dime = "NameOld.doc"
        dime2 = "NameNew.doc"
        Set YMApp = New Word.Application
        YMApp.Visible = True
        Set YMDoc = YMApp.Documents.Open(paaath & dime)
        Word.Documents(dime).SaveAs (paaath + dime2)
        For k = 1 To 6
            Windows("New.xls").Activate
            Sheets("graph").Select
            Range("L" + Trim(Str(br(k))) + ":V" + Trim(Str(br(k) + 24))).Select
            Selection.Copy
            ddd = "bm" + Trim(Str(k))
            Set B = YMDoc.Bookmarks(ddd)
            YMApp.Selection.PasteSpecial DataType:=wdPasteMetafilePicture, Placement:=B
        Next k
        YMDoc.Close
        YMApp.Quit
        Application.CutCopyMode = False
        ActiveWorkbook.Close
    End
End Sub

The problem is that by this code the bookmarks which are already created are not recognized. How to cope with the problem?

What do you mean by "the bookmarks are not recognized"? DO you get an error? Or does the output just not match your expectations? Please elaborate... — David Zemens
Sorry, for the incorrect statement. I'm not a specialist. This is like a hobby and I'm trying to reduce the time for implementation of my job. I'm not getting an error, simply the charts are not placed where the bookmarks are, but in the beginning of the sheet one over another. — Georgy

David Zemens David Zemens · Accepted Answer · 2016-06-28T14:53:33

The Placement argument of PasteSpecial does not accept a Bookmark object:

Set B = YMDoc.Bookmarks(ddd)
YMApp.Selection.PasteSpecial DataType:=wdPasteMetafilePicture, Placement:=B

Instead, it takes a WdOLEPlacement constant.

I think you'll need to select the bookmark before you do the PasteSpecial. You may need to delete existing chart (if any), also.

Untested, but I think you need something like this:

Dim wdRange as Word.Range
Set B = YMDoc.Bookmarks(ddd)
Set wdRange = B.Range

YMApp.Selection.GoTo What:=wdGoToBookMark, Name:=B.Name

' Delete existing shapes & bookmark if any:
On Error Resume Next
YMDoc.ShapeRange(1).Delete
wdRange.Delete
On Error GoTo 0
YMApp.Selection.PasteSpecial DataType:=wdPasteMetafilePicture, Placement:=0 'Or 1

'Add the bookmark back in place:
MDoc.Selection.Bookmarks.Add Name:=ddd, wdRange

copy from xls document into word using vba

1 Answers