Export chart as image - with click of a button

Question

I'm trying to create a button that would export a chart in sheet "Graphs" as a jpeg file. This is the code I have, however it keeps on showing this error:

runtime error 424: object required

Specifically for this:

Set myChart = Graphs.ChartObjects(3).Name = "Chart4"

And here's the code

Sub ExportChart()
    Dim myChart As Chart
    Dim myFileName As String
    Set myChart = Graphs.ChartObjects(3).Name = "Chart4"
    myFileName = "myChart.jpg"
    On Error Resume Next
    Kill ThisWorkbook.Path & "\" & myFileName
    myChart.Export Filename:=ThisWorkbook.Path & "\" & myFileName, Filtername:="PNG"
    MsgBox "OK"
    Set myChart = Nothing
End Sub

Thanks everyone!

score 31 · Accepted Answer · answered Aug 13 '12 at 17:25

31

Is this what you are trying?

Also if you are trying to save it as jpg then why a png filter? I have changed "myChart.jpg" to "myChart.png". Change as applicable.

Sub ExportChart()
    Dim objChrt As ChartObject
    Dim myChart As Chart

    Set objChrt = Sheets("Graphs").ChartObjects(3)
    Set myChart = objChrt.Chart

    myFileName = "myChart.png"

    On Error Resume Next
    Kill ThisWorkbook.Path & "\" & myFileName
    On Error GoTo 0

    myChart.Export Filename:=ThisWorkbook.Path & "\" & myFileName, Filtername:="PNG"

    MsgBox "OK"
End Sub

answered Aug 13 '12 at 17:25

Siddharth Rout

147,039
17
206
250

You are just too fast! One note to the OP, that's incorporated in Siddharth's answer is to add On Error GoTo 0 as soon as possible in your code, or you will mask errors. In this case you needed the ChartObject's Chart Object :) which is always confusing. – Doug Glancy Aug 13 '12 at 17:28
2

I am trying to make up for the last few days of inactivity :D – Siddharth Rout Aug 13 '12 at 17:30

Miles · Answer 2 · 2016-11-30T09:35:22.907

Thanks, I needed this to extract all charts in an image and it's almost midnight. Minor changes to the code above did the trick.

Sub ExportChart()
    Dim WS As Excel.Worksheet
    Dim SaveToDirectory As String

    Dim objChrt As ChartObject
    Dim myChart As Chart

    SaveToDirectory = ActiveWorkbook.Path & "\"

    For Each WS In ActiveWorkbook.Worksheets
        WS.Activate 'go there
        For Each objChrt In WS.ChartObjects
            objChrt.Activate
            Set myChart = objChrt.Chart

            myFileName = SaveToDirectory & WS.Name & "_" & objChrt.Index & ".png"

            On Error Resume Next
            Kill SaveToDirectory & WS.Name & Index & ".png"
            On Error GoTo 0

            myChart.Export Filename:=myFileName, Filtername:="PNG"
        Next
    Next

    MsgBox "OK"
End Sub

I ran your code but nothing was exported. Any idea why? Thanks — Tung, Oct 01 '19 at 18:00

score 0 · Answer 3 · answered Oct 18 '21 at 15:06

Working off the examples given by @Miles and @Siddarth Rout, I just created this variation. As a note, this is for the backend of an app that tracks user feedback, which needs to be presented broken out in different ways for a variety of audiences. While we wait for the developers to build the reporting end of the app, I'm downloading the data from SQL as a .csv, massaging the crap out of it in Excel, which then feeds 27 individual charts and graphs (soon to be 28 if we can get the final user group online!)

I went through and named each of the charts what I need the resultant files to be called so I can upload the graphics to the app server, overwriting the previous week's graphs, automagically (for the end users) updating the reporting.

Since the file names need to remain exactly the same, naming the graphs made the most sense, so I never need to touch that part again. (Until doing this today, I've been copying each chart, pasting into Paint, then saving as a PNG, and saving over the previous week's files so I could just click on the old one, which automatically assigns that name to the Save As process. Finding the code above and a way to make it even slicker will both save me about 30 minutes a week AND remove 27 possible manual errors... pretty good investment! If only I had made it... a year ago?)

' Ctrl-Shift-A to run

Sub ExportChart()
Dim WS As Excel.Worksheet
Dim SaveToDirectory As String

Dim objChrt As ChartObject
Dim myChart As Chart

SaveToDirectory = ActiveWorkbook.Path & "\"

For Each WS In ActiveWorkbook.Worksheets
    WS.Activate 'go there
    For Each objChrt In WS.ChartObjects
        objChrt.Activate
        Set myChart = objChrt.Chart

        ' Change from above: using ChartObject.Name to individually name each exported PNG file
        myFileName = SaveToDirectory & objChrt.Name & ".png" 
        
        On Error Resume Next
        Kill SaveToDirectory & WS.Name & Index & ".png"
        On Error GoTo 0

        myChart.Export Filename:=myFileName, Filtername:="PNG"
    Next
Next

MsgBox "Eeeaaagle!!"
End Sub

Export chart as image - with click of a button

3 Answers3

Linked