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.