Export range of cells as jpg

[JenniferMurphy]

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.

 

[RoryA]

You may also want to search for Stephen Bullen's PastePicture module. (there are examples on this forum)

You can then use the SavePicture function to save the picture directly without using a chart.

Edit: memory like a goldfish - see this thread, for example.

 

[Jerry Sullivan]

Rory, Thanks for posting the link to Stephen Bullen's PastePicture. It's much better to eliminate the need for temporary chart, but does that address the desire to have a better resolution for the jpg?

I believe Damon's suggestion to use the camera would not help improve the resolution since the earlier code examples use the same Camera function through VBA.

In puzzling around with this prior to seeing your posts it seems like changing the screen zoom (as Rick suggested) is the easiest way to address this; however the range of output resolution will be limited by zoom having a range from 10-400. This would allow creation of jpg files that use 300 dpi resolution for computers that have a 96 dpi system resolution.

 

[JenniferMurphy]

I don't understand why zoom would make any different. I don't zoom with my method and Visio is able to create an image with at least 600x600 resolution.

Here's what I do:

1. Open Visio. I'm using Visio 2003.
2. Select the cells in Excel.
3. Copy to the clipboard (Ctrl-C)
4. Switch to Visio | Paste Special (Edit | Paste Special).
5. Select an output format. I usually use Picture (Enhanced Metafile).
6. Click OK and the image appears on the Visio page.
7. With the image selected, Save As (File | Save As...).
8. Change the Save As type to JPEG, navigate to the correct folder, choose a filename, and click Save.
9. The Visio JPG Output Options dialog pops up.
10. Set the Quality to 100% (it defaults to 75%).
11. Set the resolution. I usually choose Printer, which is 600x600.
12. Set the size. I usually choose Source, which for this example is 4.802 x 3.188.
13. Click OK.

It seems to me that an image with good resolution is on the clipboard without any zooming or anything. Can't VBA get at it and do what Visio does?

Of course, this should have been built into Excel years ago (Grrrrrrr). Can't they have the Visio guys come over to the Excel group and teach them how or is this another nerdy, testosterone-besotted turf battle? (sigh)

 

[Jerry Sullivan]

Jennifer, I agree that's not a very desirable approach. Just to explain why that works that way... the Camera tool which can be called through VBA using the Range.CopyPicture method, appears to use the current resolution of the screen.

So for most people's settings, if your screen is zoomed to 100% you'll get a 96 dpi image saved to the clipboard. In actuality, the bitmap doesn't have any dpi, it just has X pixels wide by Y pixels high. The dpi can be calculated in relation to the number of pixels being displayed on the screen. If you zoom your image to 300% and use the earlier code example, you'll see that this gives you roughly 300 dpi.

Visio is using a different process to capture the screen, so it's resolution isn't limited in the same way that the Camera tool appears to be.

So a better VBA solution would use a different method to capture the image to the clipboard than the Camera. The code Rory referenced uses a Windows API OleCreatePictureIndirect. I haven't looked into this yet, but that might capture the image at a higher resolution than the Camera.

Can't they have the Visio guys come over to the Excel group and teach them how or is this another nerdy, testosterone-besotted turf battle? (sigh)

What makes you think those aren't women developers? You're from Silicon Valley!

 

[JenniferMurphy]

Visio is using a different process to capture the screen, so it's resolution isn't limited in the same way that the Camera tool appears to be.

That's my point. In my method, Visio isn't doing the screen capture, Excel is. Visio is just using whatever is on the clipboard. No?

So a better VBA solution would use a different method to capture the image to the clipboard than the Camera. The code Rory referenced uses a Windows API OleCreatePictureIndirect. I haven't looked into this yet, but that might capture the image at a higher resolution than the Camera.

Just plain old copy (Ctrl-C) seems to be capturing enough resolution. I'm not using the camera.

What makes you think those aren't women developers? You're from Silicon Valley!

I'm sure a few of them are. Otherwise, it would be much worse.

 

[Jon Peltier]

Couple comments:

1. JPG is not the appropriate format for screenshots. Use PNG.

2. Copy picture of selected range as picture (xlPicture) not as bitmap (xlBitmap).

3. Chart object has chart area border around pasted picture, which should be removed.

4. Copied range has extra pixel-wide blank edge at right and bottom. In addition, cell boundaries may occasionally be too wide if chart object is exact size as picture. So make new chart object 2 pixels wider than range, paste picture of range, then move picture right and down to center it.

Sub ExportRangeToPNG()
  '--exports selected range to png file
  '  jpg is not appropriate format for this output
  '  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
  '  modified at
  '     http://www.mrexcel.com/forum/excel-questions/751500-export-range-cells-jpg.html#post3691601
  '  further refined by Jon Peltier, Peltier Technical Services, Inc.

  Dim vFilePath As Variant
  Dim rSelection As Range
  Dim sDefaultName As String

  If TypeName(Selection) <> "Range" Then
    MsgBox "Selection is not a range of cells."
    Exit Sub
  End If

  Set rSelection = Selection

  sDefaultName = Replace(rSelection.Address(0, 0, xlA1, 1), ":", "to") & ".png"
  vFilePath = Application.GetSaveAsFilename( _
      InitialFileName:=sDefaultName, _
      FileFilter:="Portable network Graphics (*.png), *.png", _
      Title:="Save As")

  '--exit if cancelled by user
  If vFilePath = False Then Exit Sub

  '-- copy selected range as picture (not as bitmap)
  rSelection.CopyPicture Appearance:=xlScreen, Format:=xlPicture

  '--Create an empty chart, slightly larger than exact size of range copied
  With ActiveSheet.ChartObjects.Add( _
      Left:=rSelection.Left, Top:=rSelection.Top, _
      Width:=rSelection.Width + 2, Height:=rSelection.Height + 2)

    With .Chart
      ' clean up chart
      .ChartArea.Format.Line.Visible = msoFalse
      
      ' paste and position picture
      .Paste
      With .Pictures(1)
        .Left = .Left + 2
        .Top = .Top + 2
      End With
      
      ' export
      .Export CStr(vFilePath)
    End With
    
    ' remove no-longer-needed chart
    .Delete
  End With
End Sub

 

[Jerry Sullivan]

Jon, I didn't realize how much clearer non-photo content can be when saved to .png.

Your article provides a nice explanation of why...

Export Chart as Image File - Peltier Tech Blog

...and the related webcomic on your site is priceless...
Why You Should Use PNG and not JPEG - Peltier Tech Blog

Thanks much!

 

[JenniferMurphy]

Couple comments:

1. JPG is not the appropriate format for screenshots. Use PNG.

2. Copy picture of selected range as picture (xlPicture) not as bitmap (xlBitmap).

3. Chart object has chart area border around pasted picture, which should be removed.

4. Copied range has extra pixel-wide blank edge at right and bottom. In addition, cell boundaries may occasionally be too wide if chart object is exact size as picture. So make new chart object 2 pixels wider than range, paste picture of range, then move picture right and down to center it.

Jon,

This sub works, but the result is about the same as for the JPG sub. The file is about half the size (13 KB vs 24 KB). The JPG image was fuzzier, but the PNG image was broken up a little.

I ran a bunch of tests where I copied the cells to Visio using Paste Special as EMF, then saving from Visio in various formats. For those formats where Visio allowed me to choose the output resolution, all of the 96x96 outputs looked slightly fuzzy and were about the same. All of the 600x600 outputs were as clear as when printing directly from Excel.

Here's the table of results:

Output	Format	Res	Size	File Size	Quality	Filename
Visio	JPG	96x96 (Source)	Source	24 KB	Fuzzy	Visio EMF Source (96x96) Source 75%.jpg
Visio	JPG	96x96 (Source)	Source	77 KB	Fuzzy	Visio EMF Source (96x96) Source 100%.jpg
Visio	JPG	600x600 (Printer)	Source	316 KB	Clear	Visio EMF Printer (600x600) Source 75%.jpg
Visio	JPG	600x600 (Printer)	Source	888 KB	Clear	Visio EMF Printer (600x600) Source 100%.jpg
Visio	JPG	1200x1200 (Custom)	Source	2 MB	Clear	Visio EMF Custom (1200x1200) Source 100%.jpg
Visio	PNG	96x96 (Source)	Source	10 KB	Fuzzy	Visio EMF Source (96x96) Source.png
Visio	PNG	600x600 (Printer)	Source	148 KB	Clear	Visio EMF Printer (600x600) Source.png
Visio	PNG	1200x1200 (Custom)	Source	456 KB	Clear	Visio EMF Custom (1200x1200) Source.png
Visio	EMF	??	??	25 KB	Clear	Visio EMF.emf
Visio	GIF	96x96 (Source)	Source	9 KB	Fuzzy	Visio EMF Source (96x96) Source.gif
Visio	GIF	600x600 (Printer)	Source	90 KB	Clear	Visio EMF Printer (600x600) Source.gif
Visio	GIF	1200x1200 (Custom)	Source	211 KB	Clear	Visio EMF Custom (1200x1200) Source.gif
Visio		96x96 (Source)	Source	320 KB	Fuzzy	Visio EMF Source (96x96) Source.bmp
Visio		600x600 (Printer)	Source	12.5 MB	Clear	Visio EMF Printer (600x600) Source.bmp
Visio		1200x1200 (Custom)	Source	50 MB	Clear	Visio EMF Custom (1200x1200) Source.bmp
SnagIt	PNG	??	??	53 KB	Clear	Excel, print to SnagIt (cropped).png
Sub	JPG	??	--	24 KB	Fuzzy	ExportJPG Sub.jpg
Sub	PNG	??	--	13 KB	Broken lines	ExportPNG Sub.png
n/a	--	--	--	11 KB		Export Test.xlsx
n/a	--	--	--	40 KB		Export test.vsd
Excel	--	--	--	48 KB		Excel print file

<tbody>
</tbody>

 

[JenniferMurphy]

Jon, I didn't realize how much clearer non-photo content can be when saved to .png.

However, at low resolutions (96 bpi/ppi), the lines can break up in the PNG files. Then the JPG files look a little better (less bad).

At high resolutions (600+), both formats look good, but the PNG files are much smaller.

I found another solution. I can print from Excel to a graphics printer. I tested it with SnagIt and was able to capture a PNG file that was as good as anything saved from Visio. It's not much more convenient and very slow.

It would sure be great to have a subroutine that outputs at a higher (or, better yet, settable) resolution.

 

[Jon Peltier]

You can also copy the chart into PowerPoint, presumably as a picture, then from PowerPoint save it as an image file. I don't know what the quality is like, and I don't know if you can set resolution, but I suspect not. For my own purposes, the png export from the code I posted was adequate.

Using PowerPoint might be more convenient than using SnagIt or another third-party program, since you can in principle automate it all using VBA. I often use IrfanView for this kind of thing. It's manual, not VBA, but it's very quick. Actually, I use a combination of SnagIt, IrfanView, and Microsoft Paint (really!), but more IrfanView and less SnagIt lately.