1

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?

David Rogers
  • 2,601
  • 4
  • 39
  • 84
  • 1
    It is not a bug. When the Excel is in `Edit` mode, it sort of freezes completely :) – Siddharth Rout Jul 29 '16 at 17:57
  • Excel can't handle COM requests in edit mode (for obvious reasons). What is the `.Value` of a `Range` that is being actively edited? – Comintern Jul 29 '16 at 17:58
  • @Comintern "Obvious Reasons" are not obvious to me, not obvious after googling "Excel COM requests in edit mode" either, is this documented anywhere? Also the `Value` that I'm editing can be any cell in the excel document, but typically I use A1 to replicate the issue. – David Rogers Jul 29 '16 at 18:17
  • Think of it like a record lock on a database. If someone is editing a value, should you return the pre-edit value? Different databases handle that differently. Excel handles the problem by blocking. – Comintern Jul 29 '16 at 18:20
  • @Comintern Except that analogy breaks down in this example, because I should be accessing a entirely separate object(ie database), but I think I know where your going with this, I think the key is to understand that the Excel sessions are sharing information with each other, maybe http://superuser.com/questions/21602/open-excel-files-in-new-window is relevant? Correct me if I'm wrong, but the two separate excel documents(one in the PPTM and the other in the XLXS) must be aware and even dependent on each other. – David Rogers Jul 29 '16 at 18:26
  • More or less - 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. – Comintern Jul 29 '16 at 18:29
  • Missed the part about the SU link. That won't change the behavior because even if you split your Excel windows, they're still be served by the same application. You can verify by looking in the Task Manager afterward - you'll only see one instance of EXCEL.EXE. – Comintern Jul 29 '16 at 18:31
  • @SiddharthRout I'm not sure "freezes completely" accurately describes whats going on in this situation, maybe "fail with generic error message" would be allot closer. Also if its not a bug, then it must be by design, but I would describe it as "Obvious". – David Rogers Jul 29 '16 at 18:33
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/118660/discussion-between-dave13s-and-comintern). – David Rogers Jul 29 '16 at 18:33
  • `1.` Open VBA Editor in Excel `2.` Go To Sheet 1 and press F2 or double click in Cell A1. i.e put the cell in Edit mode. `3.` Go To VBA Editor and try typing in the code area. Now you will understand what I mean by "Freezing" :) – Siddharth Rout Jul 29 '16 at 18:35
  • @SiddharthRout Yeah in that example I get your point, if it's not a bug then by extension its by design, right? Maybe unavoidable, but still I don't know any other application that you can "freeze" by design.... How strange :( – David Rogers Jul 29 '16 at 18:42
  • 1
    Hope the explanation and alternative that I gave you helps you? ;) – Siddharth Rout Jul 30 '16 at 04:57

2 Answers2

1

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...

Community
  • 1
  • 1
David Rogers
  • 2,601
  • 4
  • 39
  • 84
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

enter image description here

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

enter image description here

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

enter image description here

Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250