Export chart as image - with click of a button

user_832187 0
Viewed 61821

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!

vba excel
3 Answers

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.

Renaming a graph object in Excel

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
user_60851740
Related