Problem pasting a picture from excel (2007)

[SydneyGeek]

Hi,

I haven't had a lot to do with Word VBA and I am banging my head against a wall on this one.

I am trying to paste an Excel table as a metafile in a Word document. I tried all the options with the recorder, and found that I got the following:



'paste as an enhanced metafile
Selection.PasteAndFormat (wdPasteDefault)

'paste as an Excel object
Selection.PasteSpecial Link:=False, DataType:=wdPasteOLEObject, Placement _
:=wdInLine, DisplayAsIcon:=False

'paste as a Word table, formatted like the Excel original
Selection.PasteExcelTable False, False, False

So far, so good. I want to use the metafile option so I inserted the relevant code and ran the routine -- result was not a picture, but a Word table which causes issues because the size is different and it wrecks the document layout.

I went for a dig and the wdPasteDefault enumerator is listed as 'not supported' in the 2007 Developer Help. This, despite the fact that recording the macro gave me exactly what I wanted.

Can anyone point me in the right drection please?

Denis

[Demosthine]

Good Evening.

Since you are copying the data from Excel, have you tried the CopyPicture Method? This will take a specific range and copy it to either a Picture or a Bitmap, depending on the second argument.


' Excel Macro
Range("A1:J13").CopyPicture xlScreen, xlPicture


' Word Macro
Selection.Paste


This will paste an actual Image of the Excel sheet scaled so that the entire Image will fit within the Margins. You can, of course, manipulate the Image once it's pasted.

I don't know if there's an option to specifically copy it as an Enhanced MetaFile, though.

Hope this helps or at least leads you to a solution.
Scott

[SydneyGeek]

Scott, you're a star!

That did the trick. I used

[Range].CopyPicture 1, -4147

... giving a screen-resolution picture.

Thanks
Denis

[Demosthine]

Unfortunately, I can't claim the finders fee for that one. In a project a few years ago, GTO was trying to find a way to permanently capture the data from the cells. He stumbled upon this when he was looking around. Ultimately, though, we scrapped the idea, though. It wasn't size-effective.

Scott

[SydneyGeek]

Yeah, it's funny how you stumble across these things. I hadn't seen CopyPicture before.
However, the size doesn't seem to be an issue with my setup (a table that pretty well fills the page only contributes about 11k to the file size) so it's definitely a goer for me.

Thanks again
Denis