错误获取PowerPoint Shape的.OLEFormat.Object属性(来自Excel-VBA的LateBinding) [英] Error Getting .OLEFormat.Object property of PowerPoint Shape (LateBinding from Excel-VBA)

问题描述

我有一个Excel VBA工具，它作为嵌入式OLE对象驻留在PowerPoint Presentaion中.

I have an Excel VBA tool, that resides inside a PowerPoint Presentaion as an EmbeddedOLEObject.

流程工作流:

1. 用户打开PowerPoint.
2. 然后在其中打开Excel嵌入式对象.
3. 运行其中的代码将更新Excel文件中的数据，然后将其导出到打开它的PowerPoint的第一张幻灯片中.

问题在用户打开其中两个PowerPoint演示文稿时开始.如果您打开一个Presnetation，我们将其称为" P1 "，然后打开第二个演示文稿" P2 " .然后，您在" P2 "中打开嵌入式Excel文件，Excel会卡住.在调试模式下运行时，它会在以下行中疯狂"地打开多个VBA窗口(不给出错误消息):

The problem starts when the user opens 2 of these PowerPoint presentations. If you open one Presnetation, let's call it "P1", then you open a second presentation "P2". Then you open the embedded Excel file in "P2", the excel gets stuck. When running in debug mode, it goes "crazy" opening numerous VBA windows (without giving an error message), at the following line:

Set objExcel = myShape.OLEFormat.Object.

以其他顺序运行此过程时，如果先打开" P2 "，然后打开" P1 " >"，在" P2 "中打开嵌入式Excel文件，效果很好.

When running this process the other order, If first you open "P2", and then "P1", open the Embedded Excel file in "P2" it works well.

有人知道了吗?

代码

Code

Option Explicit

Public Sub UpdatePowerPoint()

Dim ppProgram                           As Object
Dim ppPres                              As Object
Dim CurOpenPresentation                 As Object
Dim ppSlide                             As Object
Dim myShape                             As Object
Dim SlideNum                            As Integer
Dim objExcel                            As Object
Dim i                                   As Long

On Error Resume Next
Set ppProgram = GetObject(, "PowerPoint.Application")
On Error GoTo 0

If ppProgram Is Nothing Then
    Set ppProgram = CreateObject("PowerPoint.Application")
Else
    If ppProgram.Presentations.Count > 0 Then

        ' loop thorugh all open presentation, then loop through all slides
        ' check each object, check if you find an OLE Embedded object
        For i = 1 To ppProgram.Presentations.Count
            Set CurOpenPresentation = ppProgram.Presentations(i)

            Set ppSlide = CurOpenPresentation.Slides(1) ' only check the first slide for Emb. Excel objects, otherwise not a One-Pager Presentation
                For Each myShape In ppSlide.Shapes
                    Debug.Print myShape.Type & " | " & myShape.Name ' for DEBUG ONLY

                    If myShape.Type = 7 Then ' 7 = msoEmbeddedOLEObject
                        Dim objExcelwbName As String

                        '  ***** ERROR in the Line below *******
                        Set objExcel = myShape.OLEFormat.Object
                        objExcelwbName = objExcel.CustomDocumentProperties.Parent.Name ' get's the workbook name of the Emb. Object

                        If objExcelwbName = ThisWorkbook.Name Then ' compare the name of the workbook the embedded object is in, with ThisWorkbook
                            Set ppPres = CurOpenPresentation
                            GoTo ExitPresFound
                        Else
                            Set objExcel = Nothing ' reset flag
                        End If
                    End If
                Next myShape

NextPresentation:
            Set CurOpenPresentation = Nothing ' clear presentation object
        Next i

    End If ' If ppProgram.Presentations.Count > 0 Then
End If

ExitPresFound:
If ppPres Is Nothing Then ' if One-Pager presentation was not found from all open presentations
    MsgBox "Unable to Locate Presnetation, check if One-Pager Prsentation in Checked-Out (Read-Only Mode)"
End If

End Sub

推荐答案

由于目的是捕获托管嵌入式工作簿的演示文稿，并且当您确认它对您来说是一个不错的选择时，建议的解决方案是在Workbook_Open事件中捕获ActivePresentation.

Since the aim is to capture the presentation that hosts the embedded workbook, and as you confirmed that it looks to you as a good option, the suggested solution is the capture the ActivePresentation in the Workbook_Open event.

您提出的风险是合理的，理论上，有耐心的用户可能会在工作簿加载之前快速切换演示文稿，但由于存在一些安全警报，因此我无法测试这种情况的可能性wb打开之前我的测试环境，该操作的时间过长.

The risk that you raised is legitimate, it is possible (theoretically, I would say) that the impatient user switches presentations quickly before the workbook loads, but I could not test how likely is this scenario, due to some security alert in my test environment before the wb opens, giving a too large time for that action.

等待您的确认:)

这篇关于错误获取PowerPoint Shape的.OLEFormat.Object属性(来自Excel-VBA的LateBinding)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！