Exporting Charts as Images

It is very easy to export a chart as a GIF image file. The following code saves a picture of a chart to your hard drive as a GIF file:

Sub SaveChart()

Dim Cht as Chart

Set Cht = Worksheets("Pie").ChartObjects(1).Chart Cht.Export FileName:="C:/MyChart.gif", FilterName:="GIF" End Sub

This can come in useful in three possible scenarios:

■ Displaying Charts in a Web Page—You might have a Web page that displays charts. It is easy to set up the Web page to always display <Img src="MyChart.gif">. You can use Excel to import data, produce the chart, and save it as MyChart.gif.

■ Saving Resources—Charts are very memory intensive. I have seen client projects that can handle 40MB of numerical data with ease. I have also seen small 2MB files run out of memory when they have 128 charts in a workbook. If you need to display a hundred or more charts in a workbook, it might make sense to use VBA to individually create each chart, save it as a GIF file, and then load the GIF files into the workbook. The saved GIF files are not "live," but you can fit more images into the workbook than live charts without running into memory problems. To load a picture onto a worksheet, use this code:

ActiveSheet.Pictures.Insert("C:\MyChart.gif")

■ Displaying a Chart on a Userform—The only way to display a chart on a userform is to save the chart as a GIF file and then load the chart into an image on the userform. To load a chart into an Image control on a userform, use this code:

Me.Imagel.Picture = LoadPicture("C:\MyChart.gif")

0 0

Post a comment