Copy Excel Cells and Paste into Word as an Image

Question

I'm trying to capture an image of a range of cells. This image can either be saved, or pasted into the same excel document, pasted into a word document, etc. I just need to generate images from a groups of cells.

I thought I had figured it out by copying and pasting into word with this:

Dim objWord, objDoc As Object
ActiveWindow.View = xlNormalView
Range("A1:B45").Select
Selection.Copy
Set objWord = CreateObject("Word.Application")
Set objDoc = objWord.Documents.Add
objWord.Visible = True
objWord.Selection.PasteSpecial Link:=False, DataType:=wdPasteMetafilePicture, _
    Placement:=wdInLine, DisplayAsIcon:=False
objWord.Selection.TypeParagraph

When I run this, it seems to work. But the object that's pasted in word is actually a spreadsheet object that acts like a metafile. (So it's bringing the data along with it as an embedded excel sheet).

Any suggestions?

score 8 · Accepted Answer · answered Aug 19 '14 at 00:26

This appears to work:

Sub luxation()
    Dim objWord, objDoc As Object
    ActiveWindow.View = xlNormalView
    Range("A1:B45").Select
    Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture
    Set objWord = CreateObject("Word.Application")
    Set objDoc = objWord.Documents.Add
    objWord.Visible = True
    objWord.Selection.Paste
    objWord.Selection.TypeParagraph
End Sub

Copy Excel Cells and Paste into Word as an Image

1 Answers1

Linked