Animating an excel chart

[Jerry Hansen]

I want to export a chart from excel X, change the underlying range for
the chart, export the chart, change the values, export the chart...for
about 4,000 separate sets of data. Then I'm going to import them as
clips into iMovie. I tried using the export method of chart.

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Export _
FileName:="Myfilename.JPEG", FilterName:="JPEG"

It didn't work. 'Runtime error -1004' Method 'Export' of object '
Chart' failed

I checked my Graphics Filters folder, and there aren't any export
filters in it. Are there supposed to be? If so, where do I get
them...I tried looking on the Office CD, and at Microsoft.com...no
luck.

Another problem. The chart doesn't update until the VBA code is
finished running. How do I get the chart to refresh inbetween exports
(during code execution)?

 

[JE McGimpsey]

I want to export a chart from excel X, change the underlying range for
the chart, export the chart, change the values, export the chart...for
about 4,000 separate sets of data. Then I'm going to import them as
clips into iMovie. I tried using the export method of chart.

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Export _
FileName:="Myfilename.JPEG", FilterName:="JPEG"

It didn't work. 'Runtime error -1004' Method 'Export' of object '
Chart' failed

I checked my Graphics Filters folder, and there aren't any export
filters in it. Are there supposed to be? If so, where do I get
them...I tried looking on the Office CD, and at Microsoft.com...no
luck.

Another problem. The chart doesn't update until the VBA code is
finished running. How do I get the chart to refresh inbetween exports
(during code execution)?

Unfortunately, this is a bug. XLv.X can't export charts. I've reported
it to MS and it's "on the list" to get fixed. I'd suggest you send
Feedback via the Help/Feedback on Excel menu item to help move it up on
the list.

Possible workaround: When you save as web page, XL saves charts as
..gifs. Can you use those instead? If so, you can loop through and
generate a number of charts in one workbook, save as web page, then
cycle through again, changing the file name so the original charts
aren't overwritten.

There are undoubtedly others- perhaps someone else will jump in.

 

[Jim Gordon MVP]

Hi Jerry,

I'll chime in here, too.

I like McGimpsey's suggestion and would take it a step further. In the
save-as-web-page dialog box there's an "automate" button. If you enable the
automate feature excel will automatically update the exported chart every
time the file is saved. This may be faster and easier than the export chart
method since all you have to do is tell Excel to "save."

-Jim