Excel/Word Automation: Pastespecial Reopens Source File 1

[acjim]

Hi,

I'm trying to automate the copy and paste of a chart into a word file from an Excel source file (using a form as the control platform in Excel). I've managed to get this to work but instead of pasting in as a wdPasteEnhancedMetafile (which is unlinked to the excel source) the automation seems to reopen the source and paste in a chart object. This causes an OLE automation failure as 2 copies of the same workbook confuse the code (and me).

What am I doing wrong?

Here's the code:

Sub DropChart()

        Application.EnableEvents = False
		'select the main chart
         ActiveWorkbook.Charts(1).ChartArea.Select
         ActiveChart.ChartArea.Copy

         Set oWDBasic = CreateObject("Word.Application")

         ' Create a new document based on template previously created
         oWDBasic.documents.Add "C:\Documents and Settings\j1mil\Application Data\Microsoft\Templates\JnrDr.dot"
         oWDBasic.documents(1).Activate
         oWDBasic.activedocument.ActiveWindow.View.fullscreen = True
         oWDBasic.activedocument.ActiveWindow.View.fullscreen = False

         ' Insert chart into document where the bookmark 'bmChart' exists

         oWDBasic.activedocument.Range.Bookmarks("bmChart").Range.PasteSpecial Link:=False, DataType:=wdPasteEnhancedMetafile 
 
         ' Save new document to disk
         oWDBasic.activedocument.SaveAs "C:\REPORT.DOC"
    
         oWDBasic.Quit
         
        Application.EnableEvents = True
        ActiveChart.Deselect


End Sub

Any suggestions gladly received!

Jim

 

[PHV]

I'd replace this:
DataType:=wdPasteEnhancedMetafile
with this:
DataType:=9 'wdPasteEnhancedMetafile

Tip: use the Option Explicit instruction.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[acjim]

That worked a treat have a star for your troubles!

re: Option Explicit, of course! I should have remembered my vb lessons, I guess the wdConstants are not in the office reference library.

jim