PowerPoint VBA does not recognize Excel table during run

Question

I have a peice of code which copies a range from my excel file and pastes it onto my activeslide in PowerPoint. After hours of attempts to get the range from excel pasted as a table (NOT an image), I found the below code to work successfully. Note: myPP = powerpoint application.

myPP.CommandBars.ExecuteMso "PasteExcelTableSourceFormatting"
myPP.CommandBars.ReleaseFocus

The problem is that when I execute the macro, the table is pasted, but vba does not recognize the table unless the code is stepped through. I've tested this with the below code. During execution the code is skipped entirely, but during step-through it triggers.

For Each shp In activeSlide.Shapes
   If shp.HasTable Then
       MsgBox shp.Name
   End If
Next

Below is the full code. Basically I just want the excel range to be pasted into my powerpoint as a table, and for that table to be expanded to fit the slide. I'm open to suggestions in revising it a bit. Thanks for your help

Dim myPP as Object
Dim activeSlide as Object
Dim shp as Object

Worksheets("Sheet2").Activate
Worksheets("Sheet2").Range(Cells(1,1), Cells(4,7)).Copy
Worksheets("Sheet1").Activate

myPP.CommandBars.ExecuteMso "PasteExcelTableSourceFormatting"
myPP.CommandBars.ReleaseFocus

Dim myTable As String
   For Each shp In activeSlide.Shapes
       If shp.HasTable Then
           MsgBox shp.Name
           myTable = shp.Name
       End If
   Next

With activeSlide.Shapes(myTable)
      .Left = 23
      .Top = 105
      .Width = 650
      .Height = 375
End With

For ASH

Dim myPP As Object          'Powerpoint.Application
Dim myPres As Object        'Powerpoint.Presentation
Dim activeSlide As Object   'Powerpoint.Slide


Set myPP = CreateObject("Powerpoint.Application")
myPP.Visible = True
Set myPres = myPP.Presentations.Add
myPP.ActiveWindow.ViewType = 1   'ppViewSlide
Set activeSlide = myPres.slides.Add(1, 12) 'ppLayoutBlank

Did you try to add DoEvents before the loop? And to add some waiting time for the operation to finish? — A.S.H
I don't know much about doevents, but that looks promising. You might be right in that the program is moving too quickly to register the table object. I'll look into both that and waiting time when I'm at a computer and let you know the results. Thanks — Citanaf
I tried adding DoEvents & Application.Wait (Now + TimeValue("0:00:5")) before the loop. Neither individually or combined had an affect on the table. The table is still not being recognized by VBA during execution. — Citanaf
I would like to reproduce the problem. I admit this is unexpected behavior. Could you please share with me the piece of code where you set the variables myPP and activeSlide? — A.S.H
I believe they are at the top of my third snipet of code in my original post. I'm using late binding here because my users will have 2010 and 2013 excel. I'll add the code I use to start the program to my original post — Citanaf

A.S.H A.S.H · Accepted Answer · 2015-11-15T01:16:48

The problem comes from the fact that we cannot predict how much time the paste operation will last, and when it finishes. We need to wait for its completion.

' first let us count the shapes in the slide
Dim shapeCount As Integer: shapeCount = activeSlide.Shapes.Count
myPP.CommandBars.ExecuteMso "PasteExcelTableSourceFormatting"

Do '<~~ wait completion of paste operation
    DoEvents
Loop Until activeSlide.Shapes.Count > shapeCount

' Now, our table is the last in the shapes collection.
With activeSlide.Shapes(activeSlide.Shapes.Count)
    .Left = 23
    .Top = 105
    .Width = 650
    .Height = 375
End With

PowerPoint VBA does not recognize Excel table during run

1 Answers