I have an VBA Macro that at times requires me to create a new slide with a new embedded excel spreadsheet, then edit that spreadsheet. 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
About half of the time running this code results in the error:
Method object of object OLEFormat failed
This occurs on the shp.OLEFormat.Object call, I believe that this is due to "AddOLEObject" not creating the excel object in time to provide access to the property(but this is just a hypothesis). I have tried various ways of getting around this by error handling and sleep functions but so far I have been unable to create a new excel object and modify its contents within the same function without generating some error.
So my question is: How do you, with VBA, add a new embedded excel spreadsheet within a PowerPoint document and edit its contents within the same function/sub?
Update 1
I have successfully run this code on other machines, so this issue may be environmental, related with my system, and not an issue with my methodology. It also could be permission related, similar to This Post.
Update 2
I have reinstalled Office, restarted, run PowerPoint as administrator, and have added logic to account for the issue detailed in This post. Still no progress, I wonder if anyone can replicate the error that I am receiving?
I fixed the issue by resetting all my office settings by deleting all related keys in the registry(As Detailed Here):
HKEY_CURRENT_USER\Software\Microsoft\Office
After further investigation it turned out the reason I was getting this error message was because I had installed the "OLAP PivotTable Extensions"(link) add in to excel, for some reason this was conflicting with the "AddOLEObject" method. So simply deleting the registry key for the extension effectively removed the extension from excel and fixed my issue. The same effect was also observed when the extension was fully uninstalled.
HKEY_CURRENT_USER\Software\Microsoft\Office\Excel\Addins\OlapPivotTableExtensions2016