Export Chart as TIFF or JPG

[Webtechie]

Hello,

I wrote an application with Excel and VBA to create charts and then export
them as GIF. Works great. However, I then need to create a report. Crystal
Reports does not support GIF.

I need to change the code to export to TIFF or JPG. I thought I could, but
am getting an error.

Could someone please let me know how to export a chart to TIFF or JPG?


Thanks.

Here is my current code:

Set CurrentChart = Sheets("chart").ChartObjects(1).Chart

myFileName = ThisWorkbook.path & "\images\" & reviewFileName & "_" &
mPageNameStr & "_ch1.gif"

If Not FileExists(myFileName) Or iChartFlag = "Y" Then
CurrentChart.Export FileName:=myFileName, FilterName:="GIF"
End If

Me.Image1.Picture = LoadPicture(myFileName)

This works great. I tried changing GIF to TIFF or JPG. I get this error
message.

The specified dimension is not valid for the current chart type. ??!!


Thanks,

Tony

 

[Gary''s Student]

Copy the chart and paste in Paint. Then save as jpg.

 

[Alan]

This works in Excel 2003,

Sub Create_JPG()
Dim mychart As Chart
Set mychart = ActiveSheet.ChartObjects(1).Chart
mychart.Export Filename:="c:\Mychart.jpg", FilterName:="JPG"
End Sub

Regards,
Alan.

 

[Ed Ferrero]

Hi Webtechie,

What version of Excel are you using?

TIF export was available in XL 2000, but not 2003 or 2007
It does work in 2003 if you have installed both 2000 and 2003. Note that you
need to run setup to install the required graphics filters, not all are
installed by default.

I would suggest you use PNG to use charts in Crystal Report. JPG is a lossy
format.

Also, you need to change the file extension in your code for myFileName as
well as the FilterName.

eg

ActiveChart.Export path & filename & ".PNG", FilterName:="PNG"

Ed Ferrero
www.edferrero.com

 

[Webtechie]

Ed,

1) The code does not work. I am using Office 2007. I changed the code to
..png and the filtername to PNG. I get the same error.

2) I noticed you used ActiveChart. I am referencing excel charts that are
embedded in a worksheet. They are Chart sheets and I am not selecting the
chart.

I actually set a chart variable:

Set CurrentChart = Sheets("chart").ChartObjects(1).Chart

Any ideas why this isn't working for me?


Tony

 

[Webtechie]

Ed,

I also checked and according to my control panel, I have all the filters
including ONG.

Tony

 

[Ed Ferrero]

Hi Webtechie,

This works for me using XL 2007

Sub ChartXpt()
Dim CurrentChart As Chart
Dim strpath As String
Dim strFileName As String

Set CurrentChart = Sheets(1).ChartObjects(1).Chart

strpath = "C:\"
strFileName = "test"

CurrentChart.Export strpath & strFileName & ".PNG", FilterName:="PNG"

End Sub

1) The code does not work. I am using Office 2007. I changed the code
to
.png and the filtername to PNG. I get the same error.

Ed Ferrero
www.edferrero.com

 

[Jon Peltier]

The Windows filters are not the same as the Office filters.

- Jon

 

[Jon Peltier]

Embedded charts are the ones that are on a sheet, and you can select them
and move them around.

Chart sheets are the charts on a sheet without the rows and columns behind
them.

If you really have embedded charts, Ed's code will work. If you really have
chart sheets, then this code will work:

Charts("chart tab name").Export path & filename & ".PNG", FilterName:="PNG"

If you really have chart sheets, then the error is in your "Set
CurrentChart" statement.

- Jon