Save an image pasted in Excel spreadsheet through VBA

Question

The code below copies a strip of cells of a spreadsheet that is pasted as image in another spreadsheet, but I did not find a way to save it as an image file in a folder, or even select it to work with this image by VBA.

Range("D19:H25").Select

Application.CutCopyMode = False

Selection.CopyPicture Appearance:=xlScreen, Format:=xlBitmap

Sheets("Imagem").Activate

Range("A1").Select

ActiveSheet.Paste

'a instrução     Selection.CopyPicture Appearance:=xlScreen, Format:=xlBitmap
'coloca a imagem na área de transferência (imagem originada de uma faixa de células selecionadas)

'... como salvar esta imagem em uma pasta a partir da área de transferência?

'... ou como salvá-la se já estiver colada na planilha?

'... como colocar esta imagem em um objeto image, tem como?

If this pasted image (which is in the clipboard) could be saved in a folder, it could later be read to an object image of VBA.

If there is a way to take this image from the clipboard and place it directly in an image object, it would also solve, but anyway it is important to be able to save this image.

You can do it?

Answer 1

A possible solution is the following:

1 - Use the object Shape to access directly the object of the image. The image name (in my example "Image 1") is the name you can give directly in Excel, in the existing field next to the toolbar:

BTW, the minimalist image example I used was taken from here. :)

2 - Then copy the object to the clipboard. You can do as you have done, but using the object by name is easier because it does not depend on its placement in the spreadsheet.

3 - Finally, add a temporary graphic object in Excel, and paste the image into it. This "trick" is necessary for you to use the save as image of the graphic objects. (Very Ever, Watson!). The original source of this part is that reply from Soen.

Ready! Your image was written to disk in the requested file. Here’s an example of code:

Dim oImage As Shape
Dim oSheet As Worksheet
Dim oTemp As ChartObject
Dim oChartArea As Chart

Set oSheet = ActiveSheet
Set oImage = oSheet.Shapes.Item("Imagem 1")

oImage.CopyPicture

Set oTemp = oSheet.ChartObjects.Add(0, 0, oImage.Width, oImage.Height)
Set oChartArea = oTemp.Chart
oTemp.Activate
With oChartArea
    .ChartArea.Select
    .Paste
    .Export ("C:\Temp\Teste.jpg")
End With
oTemp.Delete