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.
Export Chart Procedure
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 sCurDir As String sCurDir = CurDir If ActiveSheet Is Nothing Then GoTo ExitSub If ActiveChart Is Nothing Then GoTo ExitSub Dim sPathName As String sPathName = ActiveWorkbook.Path If Len(sPathName) > 0 Then ChDrive sPathName ChDir sPathName End If Dim sFileName As String sFileName = "MyChart.png" Do Dim sChartName As String 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) Dim sPrompt As String sPrompt = "A file named '" & sFileName & "' already exists in '" _ & sPathName & "'" sPrompt = sPrompt & vbNewLine & vbNewLine sPrompt = sPrompt & "Do you want to overwrite the existing file?" Dim iOverwrite As Long 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
Add and Remove CommandBar Button
The code to add a button to the bottom of the Chart menu, and to remove the button, is pretty simple. Note that this uses the old CommandBar user interface of Excel 2003 and earlier. In Excel 2007 and later on Windows, a new ribbon tab called ‘Add-ins’ is added with an ‘Export Chart’ button. On a Mac, this will not add a button anywhere. You could also manually customize the ribbon and add the
ExportChart routine to a button.
Sub AddExportChartMenuItem(Optional bDummy As Boolean = True) RemoveExportChartMenuItem Dim vMenu As Variant vMenu = "Chart Menu Bar" Dim MyBar As CommandBar Set MyBar = CommandBars(vMenu) Dim MyPopup As CommandBarPopup Set MyPopup = MyBar.FindControl(ID:=30022) ' Data menu With MyPopup Dim MyButton As CommandBarButton 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.
Export Chart Add-In
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.