Paste graph as a picture - too slow. Any suggestions??

[Andrewdm]

The code below is used to copy all graphs on all sheets and paste them as a picture. The important thing is that the graphs are pasted back to their original position.

The code works great, except that it has slowed the process by 300% (total time for running has increased from 8 hours to 24!!). It takes about 6 seconds to copy and paste each graph.

Any suggestions on how to speed up the process. Would it be quicker to select ALL graphs on a sheet at once and paste them all at once??
------------------------------
Dim ChObj As ChartObject
Dim Top As Double
Dim Left As Double

For Each ChObj In ActiveSheet.ChartObjects
Top = ChObj.Top
Left = ChObj.Left
ChObj.Cut
ActiveSheet.Pictures.Paste.Select
Selection.Top = Top
Selection.Left = Left

Next ChObj
Next sh

 

[Richie(UK)]

Hi Andrew,

Have you tried Application.Screenupdating=False at the start of your code, and reset to True at the end?

HTH

 

[Andrewdm]

Hi

yes, I have. It doesn't seem to speed it up that much.

 

[Andrew Poulsom]

If you just want to break the links in the SERIES formulas so that they are hard numbers, this may be quicker:

Sub Test()
    Dim ChObj As ChartObject
    Dim Ser As Series
    For Each ChObj In ActiveSheet.ChartObjects
        For Each Ser In ChObj.Chart.SeriesCollection
            Ser.Values = Ser.Values
            Ser.XValues = Ser.XValues
        Next Ser
    Next ChObj
End Sub

I seem to recall that your original problem was that Excel 97 would not open charts created in Excel 2002. I don't know if this would solve that problem.

 

[Andrewdm]

Hi Andrew

Good memory!! Thanks for help a few weeks ago with the original code.

Yes, that was the problem. Some of our older systems could not handle opening too many graphs. The only way to overcome this was too paste all the graphs as a picture. After this we didn't experience any further problems at all.

The problem is speed. Our code has to cut & paste +- 12,000 graphs. Without the cutting & pasting, the reports would run in 1 minute flat. With the new code, it takes 4 to 5 minutes for each report.

 

[Andrewdm]

I tried the series code and it gives an error on one of the sheets (all the other sheets work fine).....

"Method 'X Values' of Object 'Series failed"

How do I go about fixing this

 

[Andrew Poulsom]

Does it contain a PivotChart?

 

[Andrewdm]

Is there anyone out there who can help me??

 

[Andrew Poulsom]

You haven't answered my question.

 

[Andrewdm]

No pivots charts

I tried breaking the series and name links but the older computers are still not able to open the files.

It seems that if there are more than 12 graphs in a workbook, then the older machines cannot open it. If I paste the graphs as a picture then there is no problem at all.
This message was edited by Andrewdm on 2002-10-21 11:20