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?

Answer 1

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