Excel Camera tool and a save picture button

[ReneExcel]

Hi all,

I am trying to figure out how to add a button that will save a image that was generated via the excel camera feature.

Basically I am pulling via lots of slicers data together and want the user to be able to save the image in order to attach it to emails.

Unfortunately there is only a copy feature when you right click but not a Save as feature. So I would need to figure out how to do that.

Any Ideas?

 

[pgc01]

Hi
Welcome to the board

You cannot save directly the picture.

The usual and simpler solution is to use a chartobject, since you can save a chart.

- add an empty chart to a worksheet
- resize the chart object to the dimensions of the picture
- copy the picture into the chartobject
- export the chart as picture to a file

 

[ReneExcel]

- export the chart as picture to a file

Hi,

Thanks for the welcome and the tip,

I tried that now, but how do you export the chart as a picture? there is only save as template.

Thanks a lot PGC,
Rene

 

[pgc01]

Hi Rene

You use the .Export method of the Chart object.

This is a simple example that you can try and adapt.

(amend the file pathname and the worksheet and range you want)

' PGC01 Export range as picture
Sub ExportRangePic()
Dim chto As ChartObject
Dim r As Range
Dim sPathname As String

sPathname = "c:\tmp\TestPic.jpg"

' range to be exported as picture
Set r = Worksheets("Sheet1").Range("C3:E6")

' copy range as picture
r.CopyPicture

' add a chartobject with the same dimensions as the range
With r
    Set chto = Worksheets("Sheet1").ChartObjects.Add(1, 1, .Width, .Height)
End With

' paste picture into the chart
chto.Chart.Paste

' export the chart
chto.Chart.Export Filename:=sPathname, filtername:="JPG"

' delete the auxiliary chart
chto.Delete

End Sub

 

[ReneExcel]

Thanks PGC,

does this go into a VBA code on the sheet?
I am a bit of a nomad when it comes to code in excel.
Where do I put this?

Thank you very much,
Rene

 

[ReneExcel]

Sorry I am not sure how to edit the Post I just made.

I tried to add it as an macro to the save button in the image below, but it gave me an error. also in VBA with alt +F11 didn't work, but I am not sure what I am doing there.

 

[pgc01]

Hi

In the vba editor insert a module (if it's the first it will have the name Module 1) and paste the code.
Amend the location of the file and the range and execute.

 

[ReneExcel]

Thank you very much PGC, it works almost perfect!

Two further questions, is there a way to ask the user where to save? Cannot use a path like my desktop as it will be used by many people with different paths (C: does not work as it is blocked and subfolders would be too difficult for user globally.
and the second question is, the picture it saves is a bit bad quality, how would I get it to save in a better quality.

Best,
Rene

 

[pgc01]

Hi

This code asks for the file pathname and zooms in to get better definition.
You can attach this code to the button.

Try:

Sub ExportRangePic()
Dim chto As ChartObject
Dim r As Range
Dim vPathname As Variant
Dim vZoom As Variant

' Select file pathname to save the picture
vPathname  = Application.GetSaveAsFilename(InitialFileName:="c:\tmp",  FileFilter:="Picture files (*.JPG), *.JPG", Title:="Select pathname of  picture file")
If vPathname = False Then
    MsgBox "Invalid file pathname"
    Exit Sub
End If

' range to be exported as picture
Set r = ActiveSheet.Range("C3:E9")

' set zoom and copy range as picture
vZoom = ActiveWindow.Zoom
ActiveWindow.Zoom = 200
r.CopyPicture


' add a chartobject with the same dimensions as the range
With r
    Set chto = ActiveSheet.ChartObjects.Add(1, 1, .Width, .Height)
End With

' paste picture into the chart
chto.Chart.Paste

' export the chart
chto.Chart.Export Filename:=vPathname, filtername:="JPG"

' delete the auxiliary chart and restores zoom
chto.Delete
ActiveWindow.Zoom = vZoom

End Sub

 

[ReneExcel]

PGC,

Thank you very much! this solved my task and I am very grateful!
If you have "buy me a coffee" link please do share and I will invite you.

Thank you!
Best regards,
Rene