Unable to Add OLEObject In PowerPoint When Cell is

问题:

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?

回答1:

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.

1. Open Excel
2. Add a new worksheet
3. Go To any sheet and press F2 or double click in Cell A1. i.e put the cell in Edit mode
4. Open Powerpoint
5. Add a new presentation
6. Click on INSERT | OBJECT | Microsoft Excel Worksheet (Create New) as shown in the image below

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

1. Create a new instance of Excel
2. Add a new worksheet
3. Save the blank workbook in the temp directory of the user
4. Add that file in the PowerPoint
5. Delete that file

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

'~~> API to get user's 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

回答2:

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:

Failed to Connect to Excel(Error -2147467259), Ensure that Excel is Installed and is not in "Edit Mode". Please close any open copies of Excel and try again.

The rest will depending on the user...