Export a range of cells from Excel as JPG

Question

Hi All

I found a post on here from a few years ago with this title that a Microsoft MVP (Graham Mayor) replied to with a piece of code that used SnagIt. However, he did not finish the code and because I do not understand what the code was doing I am still stuck with the same problem. I need to save a range copied to the clipboard as a jpeg file for our media department and I need to do it in a macro. I have added the code that Graham put in the post and was hoping he or someone else could complete it (as an example)?

Dim strPrinter As String

strPrinter = Excel.ActivePrinter

ActiveSheet.PageSetup.PrintArea = "$A$1:$E$40"

Application.ActivePrinter = "SnagIt 9 on Ne00:"

ActiveSheet.PrintOut

Excel.ActivePrinter = strPrinter

'What happens here..?

Many thanks in advance

Answer 1

Hi,

in the sample below

I have a range A1:I29

pic1

if you select the range A1:I29 and run the code,

you have this (picture on desktop)

pic2

if you select the range A1:F26, expected result is as follows (picture on desktop)

pic3

XXXXXXXXXXXXXXXXX

[update Sep 21, 2016]

select the desired range and run the code below:

Sub RangeAsPictureOnDesktop_01()

'Sep 12, 2016

Const FName As String = "c:\users\tasos\desktop\imagename.jpg" << result on desktop

Dim pic_rng As Range

Set pic_rng = Selection

If MsgBox("select the desired range ??", vbOKCancel) = vbCancel Then Exit Sub

Dim ShTemp As Worksheet

Dim ChTemp As Chart

Application.ScreenUpdating = False

Set ShTemp = Worksheets.Add

Charts.Add

ActiveChart.Location Where:=xlLocationAsObject, Name:=ShTemp.Name

With ActiveChart.Parent

.Height = pic_rng.Height

.Width = pic_rng.Width

End With

Set ChTemp = ActiveChart

pic_rng.CopyPicture Appearance:=xlScreen, Format:=xlPicture

ChTemp.Paste

ChTemp.Export Filename:=FName, FilterName:="jpg"

Application.DisplayAlerts = False

ShTemp.Delete

Application.DisplayAlerts = True

Application.ScreenUpdating = True

End Sub

Answer 2

Hi Frank

This too I have tried but unfortunately the area I am copying is roughly A4 size and when exporting as a chart, it becomes illegible. I guess exporting as a chart is fine for fairly small areas. That is why (frustratingly) the code above I think holds the answer, I just wish it were complete!

Thank you once again and kind regards

Andrew

Answer 3

Andrew,

This code pastes the picture of the cells in a new chart and then exports that chart.

Sub MakePicture2()

'This code copies cells $A$11:$B$21 as a picture to the clipboard

'and pastes it into a new chart and then exports that chart as a picture

    Range("$A$11:$B$21").CopyPicture Appearance:=xlScreen, Format:=xlPicture

    'Paste Image in Chart and Export it to Image file.

    ActiveSheet.Shapes.AddChart2.Select

    ActiveChart.Paste

    ActiveChart.Export Filename:="C:\VBA\SpecialChart.jpg", FilterName:="jpg"

    'Supress warning message and Delete the Chart

    Application.DisplayAlerts = False

    ActiveChart.Parent.Delete

    Application.DisplayAlerts = True

End Sub

Hope this works for you

Frank

Answer 4

Try this:

Sub MakePicture()

'This code copies cells $A$1:$E$40 as a picture to the clipboard

'You can then paste it to any other document using Ctrl+v (or paste)

 Range("$A$1:$E$40").CopyPicture Appearance:=xlScreen, Format:=xlPicture

End Sub

 

Frank

Hi Frank

thank you for the reply. Currently I can do this - i.e. reproduce this as an image in word, which looks fine, but I want to be able to save the file as an image, not as a Microsoft Office document.

Kind regards

Andrew

Answer 5

Try this:

Sub MakePicture()

'This code copies cells $A$1:$E$40 as a picture to the clipboard

'You can then paste it to any other document using Ctrl+v (or paste)

 Range("$A$1:$E$40").CopyPicture Appearance:=xlScreen, Format:=xlPicture

End Sub

Frank