Save an image pasted to Excel worksheet through VBA

Question

Save an image pasted to Excel worksheet through VBA

Navigation

#1 by (2 votes)

1

The code below copies a range of cells from a spreadsheet that is pasted to another worksheet, but I have not found a way to save it as an image file to a folder, or even select it to work with it 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 on the clipboard) could be saved to a folder, it could later be read to a VBA image object.

If you get this image from the clipboard and put it directly into an image object, it would also work, but it's important to save this image anyway.

Can you do this?

excel vba

asked by anonymous 01.02.2016 / 23:45

1 answer

How should data be selected from a table through joins? Why do divs move in place after inserting content? [closed]

score 2 · Accepted Answer

One possible solution is as follows:

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

BTW,theminimalistexampleimageIused was taken from here . :)

2 - Then, copy the object to the clipboard. You can do it the way you already did, but using the object by name is easier because it does not depend on its placement in the worksheet.

3 - Finally, add a temporary chart object in Excel, and paste the image into it. This "trick" is required for you to use the save as image of graphics objects feature. (very clever, Watson!). The original source for this part is this SOen response .

Ready! Your image was recorded on the disc in the requested file. Here is an example 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