Creating and accessing OLEObject

Question

I have a VBA macro, which sometimes requires me to create a new slide with a new built-in Excel spreadsheet, and then edit this table. A simplified version of this code:

Dim sld As Slide Dim shp As shape Dim pptWorkbook As Object Set sld = ActivePresentation.slides.add(ActivePresentation.slides.Count + 1, ppLayoutBlank) Set shp = sld.Shapes.AddOLEObject(100, 100, 100, 100, "Excel.Sheet") DoEvents If shp.Type = msoEmbeddedOLEObject Then 'Error thrown here Set pptWorkbook = shp.OLEFormat.Object pptWorkbook.Sheets(1).Cells(1, 1).value = "Stuff" End If 

In about half the cases when this code is executed, an error occurs:

OLEFormat object method object failed

This happens in a call to shp.OLEFormat.Object, I believe that this is due to the fact that "AddOLEObject" does not create an excel object in time to provide access to the property (but this is just a hypothesis). I tried various ways to get around this with the error handling and hibernation functions, but so far I have not been able to create a new excel object and change its contents within the same function without creating an error.

So my question is: how do you, with VBA, add a new embedded Excel spreadsheet to a PowerPoint document and edit its contents within the same / sub function?

Update 1

I successfully run this code on other machines, so this problem may be related to the environment associated with my system, and not with my methodology. This may also be due to a resolution similar to This Post .

Update 2

I reinstalled Office, restarted it, started PowerPoint as an administrator, and added logic to account for the problem described in this post . There is still no progress, I wonder if anyone can repeat the error I get?

Answer 1

I fixed the problem by resetting all the settings of my office, deleting all the related keys in the registry ( As detailed here ):

HKEY_CURRENT_USER \ Software \ Microsoft \ Office

After further investigation, it turned out why I received this error message because I installed the "OLAP PivotTable Extensions" ( link ) add to excel, for some reason this was contrary to the AddOLEObject method. Therefore, simply removing the registry key for the extension effectively removed the extension from excel and fixed my problem. The same effect was observed when the extension was completely removed.

HKEY_CURRENT_USER \ Software \ Microsoft \ Office \ Excel \ Addins \ OlapPivotTableExtensions2016