png

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.