Making VBA select chart and then export as picture

omahadivision · #1 10-09-2012, 08:40 PM

Hello All,

So I have what seems to be an insanely easy coding question, but it's driving me crazy. So I have this code that selects a chart and then copies it, and I wanted to make it save the chart as a picture instead of copying it. Here is the copying code:

Code:

    Dim objChart As ChartObject
       
    On Error Resume Next
    Set objChart = ActiveSheet.ChartObjects(1).Select
    
    Selection.CopyPicture _
    Appearance:=xlScreen, Format:=xlBitmap

That code works fine, so I thought I'd do

Code:

       Dim objChart As ChartObject
       
    On Error Resume Next
    Set objChart = ActiveSheet.ChartObjects(1).Select
    
    Selection.Export Filename:="C:\test.jpg", FilterName:="jpg"

Nothing ever shows up in C:\ or any other folder I've tried. I also wondered if I needed to set objChart as something else so that it as already selected, based on something I saw online:

Code:

 Dim objChart As ChartObject
        Dim slcChart As ChartObject
       
   On Error Resume Next
    Set objChart = ActiveSheet.ChartObjects(1).Select
    Set slcChart = ActiveSheet.ChartObjects(1)
    
    If ActiveChart Is Nothing Then
       MsgBox "No charts have been detected on this sheet", 0
        End If
    

    slcChart.Export Filename:="C:\test.jpg", FilterName:="jpg"

Still doesn't work. No error messages but no file either. I only have one chart in my Excel file. Can you see anything I might have done wrong?

-Dave

Colin Legg · #2 10-12-2012, 12:07 PM

So it's the chart class, not the chartobject class which has an export method. This worked fine for me (I set up a chart before running the code):

Code:

Sub foo()
    Dim cto As ChartObject
    Dim cht As Chart
    
    'get a reference to the chartobject on the sheet
    Set cto = Sheet1.ChartObjects(1)
    
    'get a reference to the chart
    Set cht = cto.Chart
    
    'export the chart as a jpg
    cht.Export "C:\Temp\MyChartPic.jpg", "JPG"
    
End Sub

omahadivision · #3 10-28-2012, 07:53 PM

Thanks! It works now! Sorry about the delay; I honestly lost the link

-Dave

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
VBA code to group picture to export as one	RenaatS	PowerPoint	3	03-05-2012 12:40 PM
Export resource custom field to excel chart	jtorres	Project	0	03-15-2011 05:16 AM
Can't select or edit chart data	itguy	PowerPoint	2	09-19-2010 10:13 AM
Making a bar chart with several data entries	Blondie1966	PowerPoint	2	04-28-2010 03:00 PM
How To Export A Picture From Word?	abrogard	Drawing and Graphics	0	08-10-2009 06:27 PM