Object OLEFormat failed -2147220995

[Guest]

I was hoping someone could shed light on my automation error:

Method 'Object' of object 'OLEFormat' failed
-2147220995

I wrote a quick test procedure that loops through several PowerPoint slides
looking for embedded Excel workbooks, then print the name of a sheet1. My
code errors on some files and not others, and I’m not sure how to fix this.
Here’s my code:

Sub TestGetWorksheetData()
Dim ppApp As PowerPoint.Application
Dim ppPres As PowerPoint.Presentation
Dim ppShape As PowerPoint.Shape
Dim xlWkb As Excel.Workbook
Dim vArray As Variant
Dim iRow As Integer
Dim iR As Integer, iC As Integer
Dim sPPT As String

On Error GoTo ErrorHandler

sPPT = Range("File")
Set ppApp = CreateObject("PowerPoint.Application")
Set ppPres = GetObject(ThisWorkbook.Path & "\" & sPPT)

iRow = 5
Sheets("test").Range(Cells(iRow, 1), Cells(iR, iC)).Clear
For x = 1 To 10
For Each ppShape In ppPres.Slides(x).Shapes
If ppShape.Type = msoEmbeddedOLEObject Then
Set xlWkb = ppShape.OLEFormat.Object
If xlWkb.Sheets(1).Name = "Detailed" Then
Debug.Print xlWkb.Sheets(1).Name & vbCr & "slide: " & x
End If
xlWkb.Close
Set xlWkb = Nothing
End If
Next
Next x
ErrorHandler:
Debug.Print Err.Description & vbCr & Err.Number

ppPres.Close
ppApp.Quit
Set ppPres = Nothing
Set ppApp = Nothing

End Sub

 

[Steve Rindsberg]

I was hoping someone could shed light on my automation error:

Method 'Object' of object 'OLEFormat' failed
-2147220995

Possibly there are OLE objects on a slide, objects that aren't Excel objects, so
when your code attempts to manipulate them as though they were .... boom. The
smoke escapes. ;-)

Possibly do this:

If ppShape.Type = msoEmbeddedOLEObject Then
If Instr(ppShape.OLEFormat.ProgID,"Excel") > 0 then ...

In other words, test to see that it's an Excel object first.
You might also want to test that it's an Excel worksheet rather than chart.