Solutions @ hans-eric.com

Problem

You want to automate the exporting of a chart within an Excel worksheet to a PNG image file using a VBA macro.

Solution

Private Sub ExportChartToFile(Worksheet as String, ChartNumber As Integer, Filename As String)
    Dim objChrt As ChartObject
    Dim myChart As Chart
    Dim FilePath As String

    Set objChrt = Sheets("Worksheet").ChartObjects(n)
    Set myChart = objChrt.Chart

    FilePath = ThisWorkbook.Path & Filename & ".png"

    On Error Resume Next
    Kill FilePath
    On Error GoTo 0

    myChart.export Filename:=FilePath, Filtername:="PNG"
End Sub

Here’s an example of its usage:

Public Sub ExportBurndownChart()
    ExportChartToFile "Burndown", 1, "Burndown Chart"
    MsgBox "Burndown chart was exported successfully"
End Sub

More Info

The code was based on the answer in a Stack Overflow question.