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

Andrewdm

New Member
Joined
May 27, 2002
Messages
34
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
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
Hi Andrew,

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

HTH
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
If you just want to break the links in the SERIES formulas so that they are hard numbers, this may be quicker:

Code:
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

New Member
Joined
May 27, 2002
Messages
34

ADVERTISEMENT

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

New Member
Joined
May 27, 2002
Messages
34
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

Does it contain a PivotChart?
 

Andrewdm

New Member
Joined
May 27, 2002
Messages
34
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
 

Forum statistics

Threads
1,144,328
Messages
5,723,730
Members
422,512
Latest member
MHau5

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top