Pasting an Excel table as a metafile in word via VBA

S

Simon Baker

I have been trying to paste a specified range of cells in Excel into Word as
an enhanced metafile, using the code below (modified from a listing in Guy
Hart-Davis' excellent book on Word VBA). If I paste as text or RTF, there is
no problem, but when I try and paste as a metafile, I get the following error:

"Run-time error 5342: The specified data type is not available"

Any thoughts?

Thanks in advance


Simon

Sub Getting_Value_from_Excel()
Dim MySpreadsheet As Excel.Workbook
Dim strSalesTotal As String
Set MySpreadsheet = _
GetObject("c:\Sales Forecast.xls")

With MySpreadsheet.Application
.Visible = True
.Parent.Windows("Sales Forecast.xls").Visible = True
.Range("salestotal").Copy
.Workbooks("Sales Forecast.xls").Saved = True
.Quit
End With

Set MySpreadsheet = Nothing

Selection.PasteSpecial DataType:=wdPasteEnhancedMetafile

End Sub
 
H

Herbert

Hi Simon,

try and place the PasteSpecial statement immediately after the copy
statement. Or at least before the quit statement.
It worked for me.

Regards,
Herbert
 
S

Simon Baker

Many thanks, Herbert. That works perfectly. This is my first foray into Word
VBA (I was warned!).

Simon
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Pasting a cell into bookmarked table from excel vba 1
Finding Hidden text in word VBA 2
Use Word vba to copy a range from Excel into Word 0
Copying from excel and pasting as a metafile in powerpoint 8
Pasting from xl into word as an enhanced metafile 0
Pasting a table created in Word as a picture 1
Word 2000 hangs when pasting a Chart from Excel as a Metafile 0
Pasting Excel selection as EMF works in Word 2003 but not 2007 5

Top