export

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

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 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.