Excel VBA - copying range to Powerpoint crashes Powerpoint instantly

Johnny C

Well-known Member
Joined
Nov 7, 2006
Messages
1,069
Office Version
  1. 365
Platform
  1. Windows
This isn't a question, I resolved it after a morning googling and watching Powerpoint stop working. I thought it might help someone else.

We have large PPs with a financial forecast (say 60 slides) and large workbooks (100 sheets) that feed them. They go through multiple iterations through levels of bureaucracy until they get signed off by the Board. So it's to be frank a right ball-ache to keep the PP updated, as a lot of the charts/tables get pasted as enhanced metafiles.

I've a plain vanilla Userform that scans the active workbook for charts and rangenames that begin with 'Copy'.
These go into a Control sheet, where the User can enter a slide number in a PowerPoint presentation for the chart or table to get copied to and decide the format (excel object or metafile)

Charts aren't a problem in either format, but when you paste a range as an Excel object, Powerpoint has a good chance of just crashing immediately. No error message, just a box saying it stopped working.

I figured Excel wasn't crashing with a lot of debug.prints, so figured it must be a synchronisation issue. I tried recoding the paste into PP in different ways, using application.wait, 3 DoEvents one after the other.

Anyway eventually I found that because it's an OLE object and Excel goes like a rocket dealing with them compared to PowerPoint, when the copying ends and the Userform unloads Powerpoint is still trying to deal with the OLE objects, which for whatever reason bombs Powerpoint.

So to get around it you need a lot of DoEvents, thanks to whoever posted the solution somewhere I forget (I've visited a lot of sites today!)

Code:
Sheets(strSheetName).Activate
Range(strRangeName).Copy
                            
' When you paste a table, excel and PP get out of sync. This loop lets them catch up with each other.
For intCount = 1 To 5000: DoEvents: Next
activeSlide.Shapes.PasteSpecial DataType:=ppPasteOLEObject, Link:=msoFalse
activeSlide.Shapes(activeSlide.Shapes.Count).Fill.BackColor.RGB = RGB(255, 255, 255)
For intCount = 1 To 5000: DoEvents: Next

I didn't know the trick with the colons either and this is my 21st year coding VBA. You never stop learning!
 
Last edited:

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Forum statistics

Threads
1,213,563
Messages
6,114,332
Members
448,566
Latest member
Nickdozaj

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
Back
Top