First, open a new PowerPoint and Excel Document, then click on the first(or any) cell in the Excel document until the text cursor is visible, switch back to PowerPoint and run the following VBA code(Assuming you have at least one blank slide already present):
ActivePresentation.slides(1).Shapes.AddOLEObject 30, 30, 100, 100, "Excel.Sheet"
I receive the following error(on multiple systems):
-2147467259 Method 'AddOLEObject' of object 'Shapes' failed
If you close the separate Excel window, the command works fine, if you select a different cell without the text cursor visible the command work fine too. Something about the fact that your editing a cell in a separate Excel window seems to cause the "AddOLEObject" method to fail.
This is one of the strangest VBA bugs I think I've ever encountered, I verified this behavior on three separate machines, and on Office versions 2013 and 2010, does anyone know why is this occurring?
To summarize all comments and to provide a road-map to anyone in the future, here are the relevant facts, and the decisions I have made in my application to avoid the issue:
You cannot send VBA commands to Excel while it is in Edit Mode, this will cause it to freeze, or if done via a external application(ie powerpoint) it will output the previously stated error message.(@SiddharthRout)
"In COM, the application is a COM server that handles requests from COM clients. Since the mode is handled on an application level and not a document level, it blocks for all documents." This means that the solution posted here will not work.(@Comintern)
This behavior is by design and not a bug(@SiddharthRout), there is no way to change the edit mode of Excel from PowerPoint, if the user has a excel window open in edit mode it is impossible to run the "AddOLEObject" command from PowerPoint.
Solution
What I have done is create a special case in my error handler to output the message:
The rest will depending on the user...
Unfortunately most of the MS Office VB Errors Messages are SHITTY!
Why Shitty? Because they are difficult to understand by a normal user. And when you click on the "Help" button in the error message, it takes you to some irrelevant link/page online or in Excel Help. I have been thinking of applying as "Error Message Writer" in Microsoft :D
After working with it for more than 18 years, I can recognize most of them but every now and then, when I come across a new error message, I actually have to search Google to find what that error means!!!
Anyways...
Like I said, "When the Excel is in Edit mode, it sort of freezes completely"
And to see this in action and to actually understand what is happening, do the following.
You will notice that you will now get a more "easy to understand error"
Alternative
We know that Excel let's you create multiple instances of Excel. So what we will do now is
Pros
You will be able to add the shape
Cons
You will not be able to work with it, till the time you are in Edit Mode. I am still trying to figure out on how to open this in separate Excel instance by double clicking on it.
Code
When you run this code, this is what you will see