Export Chart as Image File
by Jon Peltier
Thursday, June 5th, 2008
Peltier Technical Services, Inc., Copyright © 2010.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
Often people want to save their charts as an image file. Excel doesn’t offer this as a native feature in the UI, but you can export a chart using VBA:
ActiveChart.Export "C:\My Charts\SpecialChart.png"
This command allows you to export your chart in any of the bitmap formats that you have the appropriate export filters for. This varies from machine to machine, but three that I’ve always found are .PNG, .GIF, and .JPG.
Image File Format
The Chart.Export VBA command exports a chart to a variety of bitmap file formats. You should resize your chart before exporting, because a bitmap saves information for the pixels on your monitor. If you try to resize a bitmap, you may get a jagged appearance, and the weights of lines may be irregular, depending on where rows or columns of pixels are inserted or removed to achieve the new size.
You should NOT use the JPG format for a graphic like an Excel chart. The JPG compression was designed for photographic-type images, which are characterized by gradual changes in color and intensity. The compression is intended to reduce image file size by retaining optical features human eyes notice and removing optical features that human eyes don’t notice. When you have an Excel chart, containing sharp color transitions, large regions of constant color, and text, JPG makes the graphic fuzzy or muddy.
GIF is an older file format. It uses a palette consisting of the colors in the image. You can define one color of the palette to be transparent. PNG is a newer format, more flexible with colors, less flexible with transparency, at least for certain applications. PNG and GIF provide nearly identical images, and both are acceptable for Excel charts and similar graphics, but not for photographic images. Both are suitable for web pages. I generally use PNG unless I need transparency in the image.
Compare the quality of the text in this JPG screenshot (top) and the PNG screenshot (bottom). The PNG image is sharp, while the JPG text is defocused and shadowy. The JPG file is also more than twice as large as the PNG file.
Image Viewers and Editors
For a wide variety of image editing purposes, I have used the free software IrfanView for several years. It does many things very well.
I used to use Microsoft Image Editor, which came bundled with Microsoft Office 97 through 2002. Microsoft saw fit to remove it from Office 2003, and in fact, installing Office 2003 deleted it from your system. Responding to the outcry, Microsoft has published instructions to reinstall Image Editor using your old Office 2002 installation media.
There are a number of freeware image capture programs, but for several years I have used TechSmith SnagIt, a commercial program that I won’t get a dime from if you follow this link. It has a standalone program, as well as modules that run as add-ins within Office applications. This is handy if you are writing a manual or a blog, but I prefer the standalone utility.
Chart Export Procedure
The following is a simple procedure I’ve written to store the active chart as an image file in the same directory as the active workbook. One day I will enhance it with GetSaveAsFileName, but for now it’s pretty useful. I have added a button at the bottom of Excel’s Chart menu to keep it a mouse click away. Used alone, the Chart.Export command overwrites an existing file with the name entered by the user; the InputBox in this code is placed within a loop that checks for such an existing file.
Select a chart and run the procedure. When greeted by the dialog, enter a filename. If you do not enter a recognized file extension, the procedure uses PNG.
See How To: Use Someone Else’s Macro and How To: Assign a Macro to a Toolbar or Menu if you aren’t sure what to do with this procedure.
Sub ExportChart()
Dim sChartName As String
Dim sPrompt As String
Dim sDefault As String
If ActiveSheet Is Nothing Then GoTo ExitSub
If ActiveChart Is Nothing Then GoTo ExitSub
sPrompt = "Chart will be exported into directory of active workbook."
sPrompt = sPrompt & vbNewLine & vbNewLine
sPrompt = sPrompt & "Enter a file name for the chart, "
sPrompt = sPrompt & "including an image file extension (e.g., .png, .gif)."
sDefault = ""
Do
sChartName = Application.InputBox(sPrompt, "Export Chart", sDefault, , , , , 2)
If Len(sChartName) = 0 Then GoTo ExitSub
If sChartName = "False" Then GoTo ExitSub
Select Case True
Case UCase$(Right(sChartName, 4)) = ".PNG"
Case UCase$(Right(sChartName, 4)) = ".GIF"
Case UCase$(Right(sChartName, 4)) = ".JPG"
Case UCase$(Right(sChartName, 4)) = ".JPE"
Case UCase$(Right(sChartName, 5)) = ".JPEG"
Case Else
sChartName = sChartName & ".png"
End Select
If Not FileExists(ActiveWorkbook.Path & "\" & sChartName) Then Exit Do
sPrompt = "A file named '" & sChartName & "' already exists."
sPrompt = sPrompt & vbNewLine & vbNewLine
sPrompt = sPrompt & "Select a unique file name, "
sPrompt = sPrompt & "including an image file extension (e.g., .png, .gif)."
sDefault = sChartName
Loop
ActiveChart.Export ActiveWorkbook.Path & "\" & sChartName
ExitSub:
End Sub
These procedures use helper functions, which I’ve posted in File Name Functions.
I’ve enhanced this procedure to use the GetSaveAsFileName dialog, which is much nicer for the user, and also packaged the new routine into an add-in, in Enhanced Export Chart Procedure.
Related Posts:
- Enhanced Export Chart Procedure
- VBA to Split Data Range into Multiple Chart Series
- Image File Type Comparison of Exported Charts
- Robust VBA Save-As Technique
- Why You Should Use PNG and not JPEG
- Interesting Links for 17 April 2009
- Get Open or Save-As Filename
- VBA Conditional Formatting of Charts by Value
- Update Regular Chart when Pivot Table Updates
- Connect Two XY Series with Arrows (VBA)
Posted: Thursday, June 5th, 2008 under VBA.
Comments: 39
Comments
Comment from Jeff Koenig
Time: Thursday, June 5, 2008, 12:07 pm
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.
Comment from Jon Peltier
Time: Thursday, June 5, 2008, 1:16 pm
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:
Comment from JP
Time: Thursday, June 5, 2008, 10:02 pm
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
Comment from Jon Peltier
Time: Thursday, June 5, 2008, 10:11 pm
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?
Comment from derek
Time: Friday, June 6, 2008, 2:42 am
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.
Comment from Andy Pope
Time: Friday, June 6, 2008, 3:47 am
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
Comment from Tony Rose
Time: Friday, June 6, 2008, 8:54 am
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.
Comment from Jon Peltier
Time: Friday, June 6, 2008, 9:27 am
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.
Comment from Tony Rose
Time: Friday, June 6, 2008, 9:32 am
@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)
Comment from Jon Peltier
Time: Friday, June 6, 2008, 9:59 am
Tony – Great! Thanks.
Comment from derek
Time: Friday, June 6, 2008, 12:22 pm
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).
Comment from Jon Peltier
Time: Friday, June 6, 2008, 12:33 pm
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.
Comment from JP
Time: Friday, June 6, 2008, 3:04 pm
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
Comment from Jon Peltier
Time: Friday, June 6, 2008, 3:36 pm
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.
Comment from derek
Time: Friday, June 6, 2008, 3:52 pm
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.
Comment from Jon Peltier
Time: Friday, June 6, 2008, 3:56 pm
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….
Comment from derek
Time: Friday, June 6, 2008, 4:40 pm
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.
Comment from Jon Peltier
Time: Friday, June 6, 2008, 4:48 pm
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.
Comment from Reid Smith
Time: Tuesday, June 10, 2008, 12:34 pm
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
Comment from Jon Peltier
Time: Tuesday, June 10, 2008, 2:35 pm
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
Comment from Jon Peltier
Time: Tuesday, June 10, 2008, 2:42 pm
By the way, you should use the enhanced version in
Comment from Serhat
Time: Wednesday, June 11, 2008, 5:42 am
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
Comment from website design
Time: Wednesday, June 11, 2008, 12:13 pm
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/
Comment from Jon Peltier
Time: Wednesday, June 11, 2008, 1:27 pm
I’ve posted a set of helper functions used in these procedures:
Pingback from DSA Insights » Blog Archive » Excel Add-in: Export Chart Feature
Time: Thursday, June 12, 2008, 1:42 am
[...] Peliter recently wrote about the differences and advantages between saving charts as .jpg or .png files. To test [...]
Comment from Durga
Time: Monday, September 15, 2008, 11:24 am
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
Comment from Jon Peltier
Time: Monday, September 15, 2008, 12:19 pm
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.
Comment from Durga
Time: Monday, September 15, 2008, 4:29 pm
Jon
Thanks for prompt response.
Comment from Robert Dundon
Time: Friday, January 23, 2009, 9:55 am
Thanks! This blog post and the one on creating a macro (http://peltiertech.com/WordPress/2008/03/09/how-to-use-someone-elses-macro/) were helpful helpful at work today. :)
Comment from C V Horie
Time: Saturday, April 18, 2009, 2:20 am
I wish to use charts from Excel in printed publications, so the output should be >600dpi, but preferably vector based. All lines output from Excel are jaggy, but the text is not. The best way I have found so far is to print the chart with a proprietry pdf file maker at its highest resolution, then post process using Photoshop. Very cumbersome.
Do you have or know of a routine that can output high quality images, preferably vector and preferably easily edited?
Comment from Jon Peltier
Time: Saturday, April 18, 2009, 7:24 am
The export method only produces a screen shot with the same resolution as the monitor. The only way I’ve found to get higher resolution vector-based output is through something like a PDF printer. There is a good free one called PDFCreator which has a VBA interface, so that it can be controlled by Excel VBA. This would remove a lot of the tedium. Ken Puls has a number of articles which describe automation of PDFCreator using VBA.
Comment from Disbelief
Time: Monday, March 15, 2010, 5:59 am
There’s a hole in my bucket dear Lisa….
Excel cannot export a chart to a graphic file. What version is the software now? How many years has it been in development?
I wonder what logic the development team employs to justify this absolute nonsense.
Comment from Taylor
Time: Thursday, May 6, 2010, 1:29 pm
You mention different graphic export filters, with .png, .jpg and .gif as the most common. How does one go about obtaining or enabling the more obscure filters?
I am particularly interested in windows metafiles (.wmf) and enhanced metafiles (.emf?). Off the top of my head, my memory says that the vba documentation refers to the ability to export in these formats, but I have never had any success in achieving this.
Thanks!
Comment from Jon Peltier
Time: Thursday, May 6, 2010, 2:16 pm
As far as I know, Excel VBA can only deal with bitmap formats (BMP, GIF, JPB, TIF), and not all of them are reliable on all machines. I think you can paste a chart as a picture into PowerPoint, and right-click save it as a variety of types, including RMF and WMF.
Comment from Taylor
Time: Thursday, May 6, 2010, 4:03 pm
First, to confirm Jon is correct – I tracked down the graphics filters available on my computer and though there is a WMF filter (C:\Program Files\Common Files\Microsoft Shared\GRPHFLT\WMFIMP32.FLT) AND there is a registry key that gave me hope –
HKLM\SOFTWARE\Microsoft\Shared Tools\Graphics Filters\Export\WMF
I get a run-time error ‘1004′ if I try to export. This is a darn shame, as it would be ideal to be able to export a vector based graphic.
Comment from Taylor
Time: Thursday, May 6, 2010, 4:04 pm
Other interesting note:
In a nearby area of the registry -
HKLM\SOFTWARE\Microsoft\Shared Tools\Graphics Filters\Export\JPEG\Options
- there is a entry called “Quality” – so if people go against your advice (twice) and want to export a jpg version of the photorealistically fancy shaded pie chart they just whipped up* they should be able to tweak the standard quality if they can figure out the hexadecimal values needed.
*assuming this applies to Excel 2007 :)
Comment from Taylor
Time: Thursday, May 6, 2010, 4:21 pm
Re: CV Horie / Durga’s question
I was experimenting – I realized that (at least for chart sheets) the exported size of the chart doesn’t seem to be screen dependent, but rather Paper Size dependent.
So if I create a chart sheet, then set my chart to tabloid instead of letter (Page Setup > Paper Size > Tabloid) I get a bigger sized graphic output.
By selecting Page Setup > Paper Size > Postscript Custom Page Size, and then through Options (selected printer* options…) >Advanced, I was able to set a custom page size and export a 1725 x 3071 tiff chart. Much larger than my screen, and approaching respectable print size (5.8″ x 10.2″ @ 300dpi, 2.9″ x 5.1″ @ 600dpi).
*which for me was Acrobat Distiller – I think you need some sort of postscript driver to be able to select a custom page size, but I think ghostscript would do that.
Comment from Taylor
Time: Thursday, May 6, 2010, 5:16 pm
One last thing, and then I ~really~ must get some work done.
If you have Excel 2007, the xlsx files are = zip files (meaning they are containers with individual files and folders inside; if you rename one).
If you have a chart, copy said chart, paste said chart on a worksheet as an “enhanced metafile”-
You will find an emf of this picture inside the xlsx file – Book1.xlsx\xl\media\image1.emf
Don’t know exactly how useful this is – for example, the naming convention on the image doesn’t seem to be related to the source. And it looks like workaround city to be able to use this programatically.
But, on the plus side, you have a vector file of your chart (which should, in theory, be accurate if you paste it as a link). There are apparently mysterious ways of converting enhanced metafiles into scalable graphics file, but that is far outside of my area of knowledge.
All right, off to work.
Comment from Jon Peltier
Time: Thursday, May 6, 2010, 8:46 pm
Taylor -
I hadn’t thought of extracting a metafile from the xlsx file, though I have tried to process the XML files that define charts (a bit of a mess). There are libraries for dealing with zip files and Office XML files, so accessing the emf components should be possible.
Write a comment
I welcome comments from my readers. If you have an opinion on this post, if you have a question or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion.
If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.
Read the PTS Blog Comment Policy.