当单元格在单独的 Excel 窗口中处于编辑模式时，无法在 PowerPoint 中添加 OLEObject [英] Unable to Add OLEObject In PowerPoint When Cell is in Edit Mode in Seperate Excel Window

问题描述

首先，打开一个新的 PowerPoint 和 Excel 文档，然后单击 Excel 文档中的第一个(或任何)单元格，直到文本光标可见，切换回 PowerPoint 并运行以下 VBA 代码(假设您至少有一张空白幻灯片已经存在):

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 对象Shapes"的方法AddOLEObject"失败

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

如果您关闭单独的 Excel 窗口，该命令可以正常工作，如果您选择一个不同的单元格而文本光标不可见，该命令也可以正常工作.关于在单独的 Excel 窗口中编辑单元格似乎会导致AddOLEObject"方法失败这一事实.

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.

这是我认为我遇到过的最奇怪的 VBA 错误之一，我在三台不同的机器上验证了这种行为，在 Office 2013 和 2010 版本上，有人知道为什么会发生这种情况吗?

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?

推荐答案

不幸的是，大多数 MS Office VB 错误消息都很糟糕！

为什么是狗屎?因为普通用户很难理解.当您单击错误消息中的帮助"按钮时，它会将您带到一些不相关的在线链接/页面或 Excel 帮助中.我一直在考虑在 Microsoft 中申请为错误消息编写者" :D

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

在使用它超过 18 年之后，我可以认出其中的大部分，但时不时地，当我遇到新的错误消息时，我实际上必须搜索 Google 才能找到该错误的含义！！！

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

无论如何...

就像我说的，当 Excel 处于编辑模式时，它会完全冻结"

要查看实际情况并真正了解正在发生的事情，请执行以下操作.

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

1. 打开 Excel
2. 添加新工作表
3. 转到任何工作表并按 F2 或双击单元格 A1.即将单元格置于编辑模式
4. 打开幻灯片
5. 添加新的演示文稿
6. 点击插入 |对象 |Microsoft Excel 工作表(新建)如下图所示

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"

替代方案

我们知道 Excel 可让您创建多个 Excel 实例.所以我们现在要做的是

We know that Excel let's you create multiple instances of Excel. So what we will do now is

1. 创建一个新的 Excel 实例
2. 添加新工作表
3. 将空白工作簿保存在用户的临时目录中
4. 在 PowerPoint 中添加该文件
5. 删除该文件

优点

您将能够添加形状

缺点

您将无法使用它，直到您处于编辑模式.我仍在尝试弄清楚如何通过双击在单独的 Excel 实例中打开它.

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.

代码

'~~> 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

这篇关于当单元格在单独的 Excel 窗口中处于编辑模式时，无法在 PowerPoint 中添加 OLEObject的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！