Re Export Chart as Image File: Can you save a section of a worksheet as an image? I have a lot of tables that are converted into pdf for display on the web but would prefer an automated image route.

Thanks much.

Jeff -

You can copy a range of cells as a picture, paste this picture into a blank chart, and export the chart. Harald Staff has written such a procedure, and David McRitchie hosts it on his web site:

http://www.mvps.org/dmcritchie/excel/xl2gif.htm

Jon,

What’s your take on .BMP files? In my experience they come out much larger in filesize than the equivalent .JPG or .GIF image.

Also, no validation on the filename?

–JP

Hi JP -

BMPs are larger, because they contain all the color values for all pixels in the image. GIFs and PNGs are compressed by storing color data for larger areas at a time. PNGs are a loss-less format, meaning no information is lost between BMP and PNG despite the reduction in file size. I’m not sure about the lossiness of GIFs.

JPGs are a lossy file format, meaning information about the image is lost by the compression. The compression is designed so that the loss is of information that is not necessary for human vision and cognition. However, some of the loss results in the mottling and muddying of text and sharp features observed in a JPG.

My export routine includes enough filename validation to prevent overwriting an existing file with the same name. It would not be hard to adjust it to offer a choice to overwrite the existing file. Did you have something else in mind?

GIFs are lossy in color if your original image had more than 256 colors, as it only uses an 8 bit palette. A good image editing program will pick the optimum 256 color palette when saving (the bundled MS Paint program is not a good program by that criterion)

When saving PNGs, a good image editor will allow you to choose 24 bit color, for image file storage that is about as lossless as it gets. For photos this is fine. However, the question you have to ask yourself for graph images is “do I need millions of colors?” and the answer if you haven’t chosen fancy visual effects like fading backgrounds is “No!”

Color depth is the hidden cost of image file size that many people forget when they’re trying to squeeze down file size. If you have a very good image editor (I use Paint Shop Pro for all my graphs) you can choose an exact number of colors, and I find 16 colors (4 bits) or fewer will often do the job just fine.

PNG and GIF, when saving with the same small number of colors, do just about as well as each other in compression. I find GIF has the same size whether the image is interlaced or non-interlaced, interlaced PNG is a bit bigger than GIF, and non-interlaced PNG is a bit smaller than GIF.

So non-interlaced PNG, with the minimum number of bits necessary to give you all the colors you want, gets you the smallest files per displayed pixel. GIF is a very close second.

Hi Jon,

Is it worth adding that if you do not have the correct graphic filters loaded the export method will raise the following error

Run-time error ‘1004′:
Application-defined or object-defined error

I have a add-in that will export charts, shapes and ranges.
http://www.andypope.info/vba/gex.htm

Jon,

If you had a DIGG button, I would click the heck [G-rated] out of it for this post. Nice job on saving images.

Derek & Andy - Thanks for your elaborations on image formats and exporting.

Tony - Alright, I just tried adding a Digg button. The first attempt looks ugly, the second less so. I only discovered Stumble Upon the other day after someone registered a post and added over a hundred views. I guess eventually I’ll have to add a whole set of these social network buttons.

@Jon - Check out:
Addtoany –> it’s a Wordpress plugin that has one button for all of the social networks.

http://www.addtoany.com (I don’t work off commission)

Tony - Great! Thanks.

Jon, something about your claim that there was something GIFs can do that PNGs can’t was bothering me. On one hand, I seemed to remember that’s true. On the other, surely PNG was designed to be the super-GIF?

I checked on the transparency, and I was right in thinking PNGs do it fine. In fact, 24-bit color PNGs even support alpha channel transparency, that lets a picture fade at the edge, gradually bleeding into the background image. GIFs can’t do that at all.

It’s true that many browsers in use at present (*koff* MS Explorer) can’t use transparent PNGs, giving them an irritating gray background, but that’s the viewing software’s fault, not the image format’s. Does the image editing softwware you use have limited PNG transparency controls?

What was I remembering GIFs can still do that PNGs can’t? Animation. Animated GIFs were an unsupported extension of the GIF standard, and the developers of the PNG standard chose not to go there.

I’ve considered the possibilities of animated charts, but 1) I don’t really have any GIF animation editors I’m comfortable using, and 2) I would only be happy if browsers supported manual stepping back and forward through the frames. Animated GIFs in browsers are typically either on an irritating and distracting infinite loop, or turned off altogether by the fed-up user (i.e., me).

Derek -

Thanks for your follow-up.

I neglected the animation of GIFs, since I’ve never animated a GIF. Also, since animated GIFs fit within the realm of graphic junk (if not chart junk), I don’t find them worth worrying about.

I did have a client who had me automate the chart export, so that one series could be added at a time to the chart as it was exported. He used Camtasia to build animations using the exported charts, and they were not animated GIFs.

Regarding the transparency of PNG files…. IrfanView can make PNGs with transparency, but I don’t know about how it handles the alpha channel part. It may be true that the PNG transparency issue may be a browser problem, but 70% or so of my web traffic uses that browser, so I am forced to choose a technology not because of its capabilities, but because of those of supporting technologies.

I’d rather use all PNGs, because they seem cleaner to me. Since I redesigned my web site a couple years ago to use a white background, I have not needed transparency around the edges of my images to blend into the pale yellow background I once used. This renders the transparency issue moot, so I use mostly PNGs now.

I just learned far more than I needed to know about image files :)

What I was referring to by “validation” was to check the filename for invalid characters, like ? or \. You never know what someone will try to type.

ps- I stumbled this post yesterday and I also stumbled the “Indispensable Excel Utilities” post on May 9th. =)

–JP

Oh yeah, invalid characters. Well, my users never do anything like that. I wish.

I redesigned the chart export procedure to use GetSaveAsFileName, and it’s much more useful. I think I’ll post it next week in a follow up.

I would agree about passive movies, but I’d like to make an argument for the use of stepping frames back and forward, as a neglected technique in visual business intelligence, using the example of Clyde Tombaugh discovering Pluto.

Tombaugh, like astronomers before and after him, used a “blink comparator”, a device for lining up two photographic plates taken on different days, and flicking between them. With this he was able to examine a plate containing thousands of dots, which did not move from frame to frame, to pick out the one which did. I think such techniques ought to be used more in the sort of applications that mine data for insights, like Spotfire (if it does not already do so, oops on me if it does).

If I remember right, Hans Rosling is able to do this in his bubble charts, though I seem to remember the Google implementation was disappointingly uncontrolled: when I set the movie going, I was in for the ride from beginning to end at a fixed speed until it was over. Not what I want from animated VBI, thanks.

I’ve animated this kind of thing in Excel using a scrollbar to control stepping through the data. The scrollbar actually changes an index, and each series, or at least the one important series, uses an OFFSET, INDEX, or other formula that ratchets along based on the index. This might make a good post….

It’s very funny you should say that, because I was going to comment on your green label in the “Category Axis Tricks for Line and Area Charts - 1″ post, about how you presumably had a third formula there. Since I had it set up, I idly gave it a spinner, then came back to find this comment of yours. Here’s the spreadsheet.

There’s also a Juice Analytics article like that which I recommend to everybody, called Why make 100 charts when one will do?.

My idea of a great bit of standalone graphic facility would be to be able to embed a multipage GIF in a web page, and know that everyone could page back and forth through it using their own browser.

Derek -

I had that third formula in my version of the workbook, and another series in my version of the chart, with no lines or markers, just data labels showing the category label.

The Juice article is a good one. I also like the Main Effects Plot, though its key is more interaction than animation.

I don’t think I’m quite ready to try the multipage GIF or other approaches to put this on the web.

This could be very handy for me, but I’m having trouble with it. I cut/pasted the sub into a module inserted into the spreadsheet from which I’m trying to export a chart, but when I try to run the macro, I get a compile error: ‘Sub or Function not defined’ and see that FileExists is highlighted. I commented out that line and the next 5 lines and added an Exit Do and the sub works fine. I’m a stumbling VBA user and could use some help. Thanks

Reid -

My bad. The ‘FileExists’ reference is to a small routine I use to detect the presence of a file. I stole it from Karl Peterson, who was a VB6 MVP.

Function FileExists(ByVal FileSpec As String) As Boolean
  ' Karl Peterson MS VB MVP
  Dim Attr As Long
  ' Guard against bad FileSpec by ignoring errors
  ' retrieving its attributes.
  On Error Resume Next
  Attr = GetAttr(FileSpec)
  If Err.Number = 0 Then
    ' No error, so something was found.
    ' If Directory attribute set, then not a file.
    FileExists = Not ((Attr And vbDirectory) = vbDirectory)
  End If
End Function

By the way, you should use the enhanced version in
Enhanced Export Chart Procedure. The GetSaveAsFileName dialog is so much nicer and more flexible. I’m kind of kicking myself for putting off upgrading the routine until now.

Jon,

It worked out and now I’m very happy with my chart :)

I’ve copied and pasted shapes into the chart and can now save the chart with shapes embedded to chart.

Thanks for great service.

Keep up the good work!

Serhat

An improved technique is described here. This post includes a link to an add-in which does it for you.

http://peltiertech.com/WordPress/2008/06/09/enhanced-export-chart-procedure/

I’ve posted a set of helper functions used in these procedures:

File Name Functions

[...] Peliter recently wrote about the differences and advantages between saving charts as .jpg or .png files.  To test [...]

I would like to export char in tiff format with at least 300 dpi. How can I export xls chart? Is there any way to define resolution?

Thanks

Durga -

Unfortunately with this approach you are limited to screen resolution. To improve on this, you could copy the chart as a picture (metafile), paste it into a dedicated image processor, and create the image file there.

Jon

Thanks for prompt response.