Waterfall Chart
Enhanced Export Chart Procedure
by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2008. All rights reserved.
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.
Posted: Monday, June 9th, 2008 under Add-ins, VBA.
Comments: 23
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
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:
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
Write a comment