Export range of cells as jpg

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
2,482
Office Version
  1. 365
Platform
  1. Windows
According to the Microsoft website, there is no way in Excel to export a range of cells as a jpeg file. It seems to me that this would be a pretty basic function, but then what do I know.

My current solution is to copy the cells to a graphics program (Visio or Paint) and save it from there. That works, but is a bit of a nuisance.

One website suggested installing a graphics printer, such as SnagIt. I happen to have SnagIt already installed, so I tested that method. It works, but I could not figure out a way to get just the selected cells. It adds the worksheet header and footer as if I were printing it on a real printer. I guess that makes sense, but it is not what I want.

I see that there are several third party programs that say they will do the job. Can anyone vouch for any of them? I really don't need another piece of software, but if it works and has no bad side effects (such as spam from the vendor), it might be worth it.

Any suggestions appreciated.
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Hi Jennifer,

My first thought was to save the range as HTML (as is often done for send range by email code), then convert the HTML to jpg.

While looking for some free code that might do that conversion, I stumbled across a code example that exploits Excel's ability to export a Chart to jpg.

Visual Basic - VBA: Export excel range as image and save as file. | emoticode

I've modified that example to make it more interactive (using the Selected Range, and prompting for a filename).
Please let me know if this does what you wanted.

Code:
Sub ExportRangeToJPG()
 '--exports selected range to jpg file
 '  default filename is address of selected range
 '  based on code example posted at:
 '     http://www.emoticode.net/visual-basic/vba-export-excel-range-as-image-and-save-as-file.html

 Dim vFilePath As Variant
 Dim sDefaultName As String
 
 If TypeName(Selection) <> "Range" Then
   MsgBox "Selection is not a range of cells."
   Exit Sub
 End If

 With Selection
   sDefaultName = Replace(.Address(0, 0, xlA1, 1), ":", "to") & ".jpg"
   vFilePath = Application.GetSaveAsFilename( _
    InitialFileName:=sDefaultName, _
    FileFilter:="JPEG File Interchange Format (*.jpg), *.jpg", _
    Title:="Save As")
    
 '--exit if cancelled by user,
 If vFilePath = False Then Exit Sub
   '--Make picture of selection and copy to clipboard
   .CopyPicture Appearance:=xlScreen, Format:=xlBitmap
   
   '--Create an empty chart with exact size of range copied
   With ActiveSheet.ChartObjects.Add( _
        Left:=.Left, Top:=.Top, Width:=.Width, Height:=.Height)
     .Name = "TempChart"
     .Activate
   End With
 End With

 '--Paste into chart area, export to file, delete chart.
 ActiveChart.Paste
 With ActiveSheet.ChartObjects("TempChart")
  .Chart.Export CStr(vFilePath)
  .Delete
 End With
End Sub
 
Upvote 0
Wow, thanks for doing that. It works perfectly. Amazing. I would have never figured that out.

As a test, I exported a range of cells that I had previously exported by pasting them into Visio and then saving from there. Visio allows me to set the output resolution. I usually choose either the default "screen" resolution (96x96) or, if I am going to print it, the default "printer" resolution (600x600). Here are the resulting file sizes:

MethodResolutionFile SizePrint Crispness
Visio96 x 96128 KBSomewhat fuzzy
Visio600 x 6001.5 MBCrystal clear
Visio1200 x 12003.5 MBCrystal clear (same as 600x600)
Sub???38 KBSomewhat fuzzy (same as 96x96)

<tbody>
</tbody>

Is there a way to set the output resolution in the Sub?
 
Upvote 0
PS: I forgot to mention that the Visio JPG Output Options dialog provides for both the resolution and the size to be specified. I've uploaded a screen shot to this DropBox folder:

https://www.dropbox.com/sh/9x43p6tk29yuu4k/rGZSBvpO_S

That probably makes it more complicated, but it is necessary.

PPS: I don't know why this forum won't allow uploads like almost every other forum (sigh).
 
Upvote 0
Hi Jennifer,

My first thought was to save the range as HTML (as is often done for send range by email code), then convert the HTML to jpg.

While looking for some free code that might do that conversion, I stumbled across a code example that exploits Excel's ability to export a Chart to jpg.

Visual Basic - VBA: Export excel range as image and save as file. | emoticode

I've modified that example to make it more interactive (using the Selected Range, and prompting for a filename).
Please let me know if this does what you wanted.

Code:
Sub ExportRangeToJPG()
  '--exports selected range to jpg file
  '  default filename is address of selected range
  '  based on code example posted at:
  '     http://www.emoticode.net/visual-basic/vba-export-excel-range-as-image-and-save-as-file.html

  Dim vFilePath As Variant
  Dim sDefaultName As String
  
  If TypeName(Selection) <> "Range" Then
    MsgBox "Selection is not a range of cells."
    Exit Sub
  End If

  With Selection
    sDefaultName = Replace(.Address(0, 0, xlA1, 1), ":", "to") & ".jpg"
    vFilePath = Application.GetSaveAsFilename( _
     InitialFileName:=sDefaultName, _
     FileFilter:="JPEG File Interchange Format (*.jpg), *.jpg", _
     Title:="Save As")
     
  '--exit if cancelled by user,
  If vFilePath = False Then Exit Sub
    '--Make picture of selection and copy to clipboard
    .CopyPicture Appearance:=[COLOR=#FF0000][B]xlScreen[/B][/COLOR], Format:=xlBitmap
    
    '--Create an empty chart with exact size of range copied
    With ActiveSheet.ChartObjects.Add( _
         Left:=.Left, Top:=.Top, Width:=.Width, Height:=.Height)
      .Name = "TempChart"
      .Activate
    End With
  End With

  '--Paste into chart area, export to file, delete chart.
  ActiveChart.Paste
  With ActiveSheet.ChartObjects("TempChart")
   .Chart.Export CStr(vFilePath)
   .Delete
  End With
 End Sub
Is there a way to set the output resolution in the Sub?

This is just a guess on my part as I do not know for sure, but try replacing the keyword I highlighted in red in Jerry's posted code with xlPrinter and see that makes a difference.
 
Upvote 0
This is just a guess on my part as I do not know for sure, but try replacing the keyword I highlighted in red in Jerry's posted code with xlPrinter and see that makes a difference.

Nope, that gets: "Run time error '1004': CopyPicture method of Range class failed
 
Upvote 0
Okay, what happens if you remove the second argument and just use this...

.CopyPicture Appearance:=xlPrinter

That works, but it's about the same as before -- slightly fuzzy. Filesize = 35 KB.

PS: I think we need a way to specify both the resolution and the dimensions. Clearly, the image that gets copied to the clipboard from Excel has enough resolution. It just gets discarded. In a general solution, the user could chose these, but I'd be happy with the resolution being fixed at 300x300 and the dimensions equal to the source. That's what I usually choose in Visio.
 
Last edited:
Upvote 0
That works, but it's about the same as before -- slightly fuzzy. Filesize = 35 KB.

PS: I think we need a way to specify both the resolution and the dimensions.
I was hoping that xlPrinter would do that... I guess not. Sorry.

Edit Note: Just a thought... what happens if you zoom the worksheet before copying the picture... do you get more pixels in the image?
 
Last edited:
Upvote 0
Hello Jennifer,

One easy way to save a clip of a range of cells as a picture is to use the built-in Camera tool. To use this tool you must add the Camera button to your Quick Launch toolbar (Excel 2007 and later) or to the Tools menu (Excel 2003 and earlier). Assuming you are using Excel 2007 or later click the Office button and Excel Options, then the Customize button. In the Choose Commands from dropdown select Commands Not in the Ribbon. Select the Camera button and click Add >> to add it to the Quick Access toolbar.

Once added to the QAT, simply select the range of cells you want to convert to a picture and click the Camera button. Then click on the sheet where you would like the picture inserted. To save it as a jpg, simply copy it and paste (or import clipboard) into any picture editor (I highly recommend XnView, which is a free download from XnView.com. It is very fast, easy, and lots of functionality), then Save as jpeg file type.

Note that the picture the Camera button creates is linked to the cells, so it updates automatically when you change any of the cells in the range. Nevertheless you can use the picture editing features in Excel to modify it (e.g., change the contrast, brightness).

I hope you find this helpful.

Damon
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top