Unable to add OLEObject to PowerPoint When a cell is in edit mode in a separate Excel window

Question

First open a new PowerPoint and Excel document, then click on the first (or any) cell in the Excel document until the text cursor appears, go back to PowerPoint and run the following VBA code (suppose you have at least one empty slide already present) :

ActivePresentation.slides(1).Shapes.AddOLEObject 30, 30, 100, 100, "Excel.Sheet" 

I get the following error (on multiple systems):

-2147467259 Method 'AddOLEObject' of object 'Shapes' failed

If you close a separate Excel window, the command works fine, if you select another cell without a visible text cursor, the command also works fine. Something about your editing a cell in a separate Excel window causes the AddOLEObject method to crash.

This is one of the strangest VBA errors I have ever encountered, I tested this behavior on three separate machines, and in versions of Office 2013 and 2010 does anyone know why this is happening?

Answer 1

Unfortunately, most MS Office VB error messages are SHITTY!

Why shitty? Because they are difficult for ordinary users to understand. And when you click the " Help " button in the error message, you can go to some irrelevant link / page on the Internet or in Excel help. I was thinking about using Writer Message Message in Microsoft: D

After working with it for more than 18 years, I can find out most of them, but from time to time, when I come across a new error message, I really need to search Google to find out what this error means !!!

Anyway...

As I said, "When Excel is in edit mode, it completely freezes"

And to see this in action and actually understand what is happening, do the following.

• Open excel
• Add new sheet
• Go to any sheet and press F2 or double-click in cell A1 . i. Put the cell in edit mode
• Open powerpoint
• Add New Presentation
• Press INSERT | OBJECT | Microsoft Excel Worksheet (Create New) as shown in the figure below.

You will notice that now you will get a more “ easy to understand error ”

---

Alternative

We know that Excel allows you to create multiple instances of Excel. So now we will do

• Create a new Excel instance
• Add new sheet
• Save an empty book in the user's temp directory
• Add this file to PowerPoint
• Delete this file

Pros

You can add a form

vs

You cannot work with it until you are in edit mode. I'm still trying to figure out how to open this in a separate instance of Excel by double clicking on it.

code

 '~~> API to get user temp path Private Declare Function GetTempPath Lib "kernel32" Alias "GetTempPathA" _ (ByVal nBufferLength As Long, ByVal lpBuffer As String) As Long Private Const MAX_PATH As Long = 260 Sub Sample() Dim oxlapp As Object, oxlwb As Object Dim filePath As String '~~> Create a temporary file name filePath = TempPath & Format(Now, "ddmmyyhhmmss") & ".xlsx" '~~> Create a new instance Set oxlapp = CreateObject("Excel.Application") '~~> Add a new workbook Set oxlwb = oxlapp.workbooks.Add '~~> Save it to the temp directory oxlwb.SaveAs filePath, 51 '~~> Add the shape ActivePresentation.Slides(1).Shapes.AddOLEObject 30, 30, 100, 100, , filePath, msoFalse, , , , msoFalse oxlwb.Close (False) oxlapp.Quit Kill filePath End Sub Function TempPath() As String TempPath = String$(MAX_PATH, Chr$(0)) GetTempPath MAX_PATH, TempPath TempPath = Replace(TempPath, Chr$(0), "") End Function 

When you run this code, this is what you will see

Answer 2

To summarize all the comments and provide a roadmap to anyone in the future, here are the relevant facts and decisions I made in my statement to avoid the problem:

• You cannot send VBA commands to Excel while it is in edit mode, this will cause it to freeze or, if done through an external application (for example, PowerPoint), it will display the previously indicated error message. (@SiddharthRout)

• "In COM, an application is a COM server that processes requests from COM clients. Since the mode is processed at the application level and not at the document level, it blocks all documents." This means that the solution posted here will not work. (@Comintern)

• This design behavior is not an error (@SiddharthRout), there is no way to change the Excel editing mode from PowerPoint, if the user has the Excel window in editing mode, it is not possible to run the "AddOLEObject" command from PowerPoint.

Decision

What I did was create a special case in my error handler to display the message:

Failed to connect to Excel (error -2147467259), make sure Excel is Installed and is not in "edit mode". Close all open copies of Excel and try again.

The rest will depend on the user ...