Enhanced Export Chart Procedure

by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2009.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.

In Export Chart as Image File I described the VBA command to export a chart as an image file, and I presented a simple routine that exported the active chart.

I said that one day I would enhance the procedure to use GetSaveAsFileName, which allows the user to browse to any folder to save the image file, rather than blindly dumping it into the directory containing the active workbook. I also thought it would be nice not only to detect a duplicate file name, but also to allow the user to overwrite the duplicate with a new version of the same file. The older procedure did not check for invalid characters, while the new procedure relies on the GetSaveAsFileName dialog to accomplish this.

Without further ado, here is the enhanced procedure. After using it for fifteen minutes, the abilities to browse to another folder, to see the files in the target directory, and to overwrite an existing file had already paid for the effort to rewrite the code.

The ExportChart module and the menu modifying code below should go into a regular module, probably in an add-in or other workbook you will use to contain the code.

Sub ExportChart()
  Dim sChartName As String
  Dim sFileName As String
  Dim sPathName As String
  Dim sPrompt As String
  Dim sCurDir As String
  Dim iOverwrite As Long

  If ActiveSheet Is Nothing Then GoTo ExitSub
  If ActiveChart Is Nothing Then GoTo ExitSub

  sCurDir = CurDir
  sPathName = ActiveWorkbook.Path
  If Len(sPathName) > 0 Then
    ChDrive sPathName
    ChDir sPathName
  End If

  sFileName = "MyChart.png"

  Do
    sChartName = Application.GetSaveAsFilename(sFileName, "All Files (*.*),*.*", , _
        "Browse to a folder and enter a file name")

    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)) = ".BMP"
        'Case UCase$(Right(sChartName, 4)) = ".TIF"
        'Case UCase$(Right(sChartName, 5)) = ".TIFF"
      Case UCase$(Right(sChartName, 4)) = ".JPG"
      Case UCase$(Right(sChartName, 4)) = ".JPE"
      Case UCase$(Right(sChartName, 5)) = ".JPEG"
      Case Else
        If Right$(sChartName, 1) <> "." Then sChartName = sChartName & "."
        sChartName = sChartName & "png"
    End Select

    If Not FileExists(sChartName) Then Exit Do

    sFileName = FullNameToFileName(sChartName)
    sPathName = FullNameToPath(sChartName)

    sPrompt = "A file named '" & sFileName & "' already exists in '" & sPathName & "'"
    sPrompt = sPrompt & vbNewLine & vbNewLine & "Do you want to overwrite the existing file?"

    iOverwrite = MsgBox(sPrompt, vbYesNoCancel + vbQuestion, "Image File Exists")

    Select Case iOverwrite
      Case vbYes
        Exit Do
      Case vbNo
        ' do nothing, loop again
      Case vbCancel
        GoTo ExitSub
    End Select
  Loop

  ActiveChart.Export sChartName

ExitSub:

  ChDrive sCurDir
  ChDir sCurDir
End Sub

The code to add a button to the bottom of the Chart menu, and to remove the button, is pretty simple:

Sub AddExportChartMenuItem(Optional bDummy As Boolean = True)
  Dim MyBar As CommandBar
  Dim MyPopup As CommandBarPopup
  Dim MyButton As CommandBarButton
  Dim iMenu As Long
  Dim vMenu As Variant

  RemoveExportChartMenuItem

  vMenu = "Chart Menu Bar"

  Set MyBar = CommandBars(vMenu)
  Set MyPopup = MyBar.FindControl(ID:=30022)  ' Data menu

  With MyPopup

    Set MyButton = .Controls.Add(Type:=msoControlButton)
    With MyButton
      .Caption = myChartExportMenu
      .Style = msoButtonIconAndCaption
      .FaceId = 435  ' 422
      .BeginGroup = True
      .OnAction = "'" & ThisWorkbook.Name & "'!ExportChart"
      .Visible = True
    End With

  End With

End Sub

Sub RemoveExportChartMenuItem(Optional bDummy As Boolean = True)
  Dim vMenu As Variant

  vMenu = "Chart Menu Bar"

  On Error Resume Next
  CommandBars(vMenu).FindControl(ID:=30022).Controls(myChartExportMenu).Delete
  On Error GoTo 0

End Sub

These procedures use a constant that contains the label text for the button, so in the declarations section of the module you should insert this line:

Public Const myChartExportMenu As String = "E&xport Chart"

Call these menu modifying procedures from the ThisWorkbook module of the workbook or add-in containing the chart export and menu modifying code, using the following event procedures.

Private Sub Workbook_AddinInstall()
   AddExportChartMenuItem
End Sub

Private Sub Workbook_Open()
  AddExportChartMenuItem
End Sub

Private Sub Workbook_AddinUninstall()
  RemoveExportChartMenuItem
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  RemoveExportChartMenuItem
End Sub

The procedures above call a few helper functions, which are posted in File Name Functions.

I’ve wrapped this procedure into a small self-contained add-in, which you can download from here:
ExportChart.zip. Unzip the file and install the add-in, as described in Installing an Excel Add-In. The add-in adds a button, Export Chart, to the bottom of the Chart menu, which will export the active chart.

Possibly Related Posts:

Bookmark and share this entry:

Posted: Monday, June 9th, 2008 under VBA.
Comments: 35

Comments

Comment from Consultant Ninja
Time: Monday, June 9, 2008, 9:04 am

I wrote a similar function here, but included the ability to export shapes or ranges of cells.

http://www.consultantninja.com/2007/11/exporting-excel-charts-shapes-ranges.html

Comment from Jon Peltier
Time: Monday, June 9, 2008, 9:34 am

Ninja – Nice one. I’d missed it earlier. It’s based on a RangeToGif procedure developed by Harald Staff, and posted here:

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

I made a couple of comments on your post, regarding the treatment of charts in your routine.

For ranges, I’ve cobbled together a little routine that converts the selected range in Excel into HTML, with options to copy various cell and font formatting, and to show or not show the row and column headers. It’s pretty nice, but it doesn’t render as nicely in FireFox as in IE (duh, since I borrowed from MS Excel export-to-html formatting). I haven’t posted this export routine, but maybe if I’m hungry for a blog topic some day, or if someone reads this comment and asks nicely….

Comment from Serhat
Time: Tuesday, June 10, 2008, 10:55 am

Hi Jon,

I’ve followed the steps and added the add-in and copied the macro code. But when I try to run the macro I get the “Compile Error, Sub of Function Not Defined” message.

Would you please help?

Comment from Jon Peltier
Time: Tuesday, June 10, 2008, 11:27 am

The add-in is self-contained. If you install the add-in, you don’t need to copy or paste any code.

Comment from Serhat
Time: Tuesday, June 10, 2008, 1:03 pm

Thanks, now it works. But I had a problem: The add-in didn’t work when I grouped some drawings with the chart. It seems that when you group a chart with something, Excel don’t count it as a chart. Any idea?

Comment from Tony Rose
Time: Tuesday, June 10, 2008, 2:29 pm

Umm, I love this add-in for Excel. It’s very easy to install and a breeze to use! Thanks Jon! Now I need to see how much better my graphs would be as a png. vs. jpg..

Comment from Jon Peltier
Time: Tuesday, June 10, 2008, 2:39 pm

Serhat – The routine works on the active chart. Once you’ve grouped the chart with other objects, it cannot be the active chart. Are the shapes something that could be pasted into the chart itself? Ungroup the chart from the objects, copy the objects, select the chart, then paste. The shapes are now integral with the chart. Unfortunately, shapes in a chart are not always faithfully rendered when exporting.

Tony – This routine makes it easy. Run it once, and name the chart Chart.jpg, then run it again using Chart.png. (In fact, if you leave off the extension, or use one that Excel doesn’t recognize, it will default to png.)

Comment from Tony Rose
Time: Tuesday, June 10, 2008, 2:48 pm

I’m going to run my chart with both extensions and post a new blog entry comparing the two.

Comment from Eric Chan
Time: Wednesday, June 11, 2008, 12:33 pm

Can you please post the function for FullNameToFileName()?
It says that the function is not defined in your code.

Comment from Jon Peltier
Time: Wednesday, June 11, 2008, 12:37 pm

Eric -

Another helper function I use a lot and forgot to post.

Function FullNameToFileName(sFullName As String) As String
  Dim k As Integer
  Dim sTest As String
  If InStr(1, sFullName, "[") > 0 Then
    k = InStr(1, sFullName, "[")
    sTest = Mid(sFullName, k + 1, InStr(1, sFullName, "]") - k - 1)
  Else
    For k = Len(sFullName) To 1 Step -1
      If Mid(sFullName, k, 1) = "\" Then Exit For
    Next k
    sTest = Mid(sFullName, k + 1, Len(sFullName) - k)
  End If
  FullNameToFileName = sTest
End Function

Comment from Eric Chan
Time: Wednesday, June 11, 2008, 12:45 pm

Can you please post the function for FullNameToPath() as well?
Thank you!

Comment from Jon Peltier
Time: Wednesday, June 11, 2008, 1:24 pm

Eric – I’ve posted a set of helper functions here:

File Name Functions

Comment from Eric Chan
Time: Wednesday, June 11, 2008, 3:34 pm

Is it possible to export the charts onto a network server instead of the local computer?

Comment from Jon Peltier
Time: Wednesday, June 11, 2008, 3:52 pm

Eric -

If you can browse to the directory in the Save As dialog, and if you have the right permissions, you can save the file to any share. I just saved an image on my office network to

\\pts1\SharedDocs\Web\MyChart.png

Comment from Sue
Time: Friday, July 11, 2008, 3:09 pm

Thanks a lot. My boss was just asking if I knew of a way to save charts as images so she can import them into Movie Maker. This works great. I dabble in VBA but couldn’t have come up with this. Now I can study it and learn something.

Comment from Ben Armine
Time: Monday, August 11, 2008, 9:01 am

I have installed the add-in on Excel 2000 (past its sell by date!). The Chart menu shows the Export button. Activating the button brings up a dialogue box with File Name (”MyChart.png”) and Save as Type (All Files (*.*)).

Using the default settings (or any other) gives a run-time error 1004 with the message Method ‘Export’ of object ‘_Chart’ failed. Opening the debugger showed the error at

ActiveChart.Export sChartName

[edited for length]

The VB editor indicates the routine has acquired the correct file location.

Ben
Not being a code cruncher can anyone help?

Comment from Jon Peltier
Time: Tuesday, August 12, 2008, 8:54 am

Ben -

What is the save as file name (sChartName)? To make this easier, insert this line in front of “ActiveChart.Export sChartName”, and rerun the utility:

Debug.Print sChartName

Is the save as directory one which you have sufficient write permissions (i.e., are you permitted to create a file in that directory)?

Can you select a chart, open the Immediate Window (Ctrl+G) in the VB Editor, and execute this command:

activechart.export “C:\mychart.png”

How about this command:

activechart.export “C:\mychart.gif”

Comment from Jon Peltier
Time: Tuesday, August 12, 2008, 8:55 am

Further investigation showed the PNG Filter had not been loaded when Office 2000 was installed. The Add-in is now working beautifully.

Experience also shows the difference between using png / gif file format compared to jpg.

Many thanks for all the work you have done to help others extract the best from Excel.

Ben [JP]

Comment from Charlie
Time: Tuesday, September 9, 2008, 12:05 am

I have Office 2007, and I’m not seeing a button anywhere to export the chart…
I like the idea though, it looks like it works.

Comment from Jon Peltier
Time: Tuesday, September 9, 2008, 7:42 am

Charlie -

There should be a button on the Add-Ins tab for “Export Chart”.

Incidentally, I tried to add the button to the right click menu, which would work in old and new versions of Excel. But in their rush to release 2007, Microsoft forgot to make the chart context (right click) menus accessible to changes through VBA.

Comment from stan
Time: Tuesday, September 9, 2008, 6:39 pm

Jon:

I have a 4 by 4 array of charts ( all same size) and want to export it to a single image file (.gif).

How would you do it in a VB script?

Comment from Jon Peltier
Time: Tuesday, September 9, 2008, 8:08 pm

Stan -

Essentially you would copy the charts as pictures, paste them into a larger chart, and export the larger chart.

Harald Staff wrote a procedure to copy a range of cells into a chart for export. Perhaps you could adapt it. David McRitchie hosts Harald’s procedure here:

XL2GIF routine and manipulations

Comment from Charlie
Time: Wednesday, September 10, 2008, 3:56 am

Jon,

I forgot to activate the Add-in! It works awesome, great job. I can’t believe something like this isn’t standard. Thanks a bunch.

Charlie

Comment from Kenneth Farrell
Time: Saturday, January 10, 2009, 7:46 pm

Jon, presently I scan my scientific-type Excel plots into a graphics program to tidy them up before submitting them for publication. I use a clone tool to erase unwanted tick marks and scale labels from secondary axes where I am using only as little as one third of the full axis. And I crop out excess chart area around the plot area. I also add axis break marks, and I relabel logarithmic axes to an exponential format. I now use your Exponential Notation route for the latter. l love it, thanks. I plan to use your Enhanced Export Chart Procedure to export my untidy plots from Excel directly to the graphics program. Does this path retain the Excel construction features in the plots? If so, is there a way to import the tidied up plot back into Excel where I can make the modified plots into templates and/or do further editing?
Sincerely, Ken Farrell

Comment from Jon Peltier
Time: Saturday, January 10, 2009, 11:13 pm

Ken -

The exported charts are in a raster format (i.e., pixel by pixel), so cannot serve as templates, and may not be useful as raw material for a graphics program. PowerPoint allows exporting of objects in vector formats (consisting of drawing objects), and I have considered going this route with Excel charts, but have never been sufficiently motivated.

However, you can make a lot of the changes you describe in your Excel charts, to minimize the manual work you need to do in third party packages. The excess white space can be shrunk by expanding the plot area size. The unwanted axis tick labels can be hidden through use of custom number formats. Unwanted tick marks can be avoided by not using built in tick marks, but instead positioning a hidden XY series along the axis, one point everywhere you want a tick mark, and add custom error bars as custom tick marks.

Pingback from Export Excel Chart as .PNG « Hacks Ahoy
Time: Monday, January 26, 2009, 6:52 pm

[...] January 26, 2009 I ran across a very useful little add-in for Excel today. My hatred for Microsoft notwithstanding, I’m forced to use Office for much of what I do at my job. Several times over the past year, I’ve found myself needing to include charts from Excel in presentations, documents, or even just through email. Most people send the entire spreadsheet or just copy and paste it as a BMP. I’ve found that a lot of the time, it’s nearly impossible to read or just a royal pain to deal with. No longer a problem. [...]

Comment from Andrew Smyth
Time: Wednesday, March 25, 2009, 10:53 am

The macro is not working properly as I am getting a runtime error 5. Any ideas as to why? Thanks.

Comment from Jon Peltier
Time: Wednesday, March 25, 2009, 11:19 am

Did you get any indication of which command failed? Did you see whether the code in this article works?

Comment from Andrew Smyth
Time: Wednesday, March 25, 2009, 1:44 pm

The debugger highlights this line: “ChDrive sPathName”.
Could this be a problem with the 64 bit XP that I am running? I have had problems previously with other programs. Since I am in a hurry I am just manually using the “ActiveChart.Export” command, but I would really like your macro for future use. Also, on a side note, does this command have any option to control the resolution? I would prefer some higher resolution TIFF files.

Comment from Jon Peltier
Time: Wednesday, March 25, 2009, 6:14 pm

I’m not sure why that line would fail. I have a trap that bypasses the line if sPathName is “”, or rather, if the length of the string in sPathName is zero. Is the workbook stored on a network share, perhaps, or opened from email or from a zip file?

You could change the section of code to

On Error Resume Next
    ChDrive sPathName
    ChDir sPathName
On Error Goto 0

The charts exported as image files have no greater resolution than the screen.

Comment from Bob
Time: Sunday, March 29, 2009, 10:09 pm

Could it be SP3?

Cheers,

Bob

Comment from Jon Peltier
Time: Sunday, March 29, 2009, 10:19 pm

SP3 of ?? I don’t see how that would be a problem. I did just notice the problem occurred on 64-bit XP, but to have this fail on such a core function boggles the mind. Although Microsoft has boggled many minds with various upgrades and updates.

Comment from julian
Time: Monday, June 1, 2009, 4:39 am

Excellent! This works great, and I could never have done it myself. Thanks :)

Comment from Matthew McGlone
Time: Tuesday, August 25, 2009, 5:30 pm

Thanks for the great tool. Is there a way to specify the desired output resolution? I need a specific size (x,y) and find that resizing in Paint or other graphic editors to distort the chart image.

Comment from Jon Peltier
Time: Thursday, August 27, 2009, 1:26 pm

Matthew -

You need to make the chart the proper size to begin with. You should do int manually, and then make sure the chart looks okay.

To do it programmatically, before exporting multiply the size in pixels by 0.75 to specify the size in points (for a typical monitor setting). For example, to export the chart as a 400×600 pixel image:

ActiveChart.Parent.Height = 400 * 0.75
ActiveChart.Parent.Width = 600 * 0.75

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.

Custom Solutions | Commercial Utilities | Training | Charts and Tutorials | PTS Blog

Main menu:

Peltier Tech
Chart Utilities

Subscribe

Site search

Subscribe

Site search

Recent Posts

Recently Commented

Popular Posts

Archive

Categories

Buy me a coffee