Excel VBA - copying range to Powerpoint crashes Powerpoint instantly

[Johnny C]

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!)

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!

 

[Johnny C]

This is the thread that explained it:-
https://stackoverflow.com/questions/16338295/excel-to-powerpoint-vba-pastespecial-keep-source-formatting