Excel VBA - copying range to Powerpoint crashes Powerpoint instantly
Results 1 to 2 of 2

Thread: Excel VBA - copying range to Powerpoint crashes Powerpoint instantly
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular Johnny C's Avatar
    Join Date
    Nov 2006
    Location
    Liverpool, UK
    Posts
    974
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Excel VBA - copying range to Powerpoint crashes Powerpoint instantly

    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 by Johnny C; Jun 6th, 2019 at 09:44 AM.
    "If you think this Universe is bad, you should see some of the others" - Philip K. DiĘk

  2. #2
    Board Regular Johnny C's Avatar
    Join Date
    Nov 2006
    Location
    Liverpool, UK
    Posts
    974
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel VBA - copying range to Powerpoint crashes Powerpoint instantly

    This is the thread that explained it:-
    https://stackoverflow.com/questions/...rce-formatting
    "If you think this Universe is bad, you should see some of the others" - Philip K. DiĘk

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •