excel - PowerPoint VBA 在运行期间无法识别 Excel 表格

Question

我有一段代码，它从我的 Excel 文件中复制一个范围并将其粘贴到 PowerPoint 中的事件幻灯片上。经过几个小时的尝试将 Excel 中的范围粘贴为表格(而不是图像)，我发现下面的代码可以成功工作。注意:myPP = powerpoint 应用程序。

myPP.CommandBars.ExecuteMso "PasteExcelTableSourceFormatting"
myPP.CommandBars.ReleaseFocus

问题是，当我执行宏时，表格被粘贴，但 vba 无法识别该表格，除非逐步执行代码。我已经用下面的代码对此进行了测试。在执行期间，代码会被完全跳过，但在单步执行期间会触发。

For Each shp In activeSlide.Shapes
   If shp.HasTable Then
       MsgBox shp.Name
   End If
Next

下面是完整的代码。基本上，我只想将 Excel 范围作为表格粘贴到我的 powerpoint 中，并扩展该表格以适合幻灯片。我愿意接受稍微修改一下的建议。感谢您的帮助

Dim myPP as Object
Dim activeSlide as Object
Dim shp as Object

Worksheets("Sheet2").Activate
Worksheets("Sheet2").Range(Cells(1,1), Cells(4,7)).Copy
Worksheets("Sheet1").Activate

myPP.CommandBars.ExecuteMso "PasteExcelTableSourceFormatting"
myPP.CommandBars.ReleaseFocus

Dim myTable As String
   For Each shp In activeSlide.Shapes
       If shp.HasTable Then
           MsgBox shp.Name
           myTable = shp.Name
       End If
   Next

With activeSlide.Shapes(myTable)
      .Left = 23
      .Top = 105
      .Width = 650
      .Height = 375
End With

对于 ASH

Dim myPP As Object          'Powerpoint.Application
Dim myPres As Object        'Powerpoint.Presentation
Dim activeSlide As Object   'Powerpoint.Slide


Set myPP = CreateObject("Powerpoint.Application")
myPP.Visible = True
Set myPres = myPP.Presentations.Add
myPP.ActiveWindow.ViewType = 1   'ppViewSlide
Set activeSlide = myPres.slides.Add(1, 12) 'ppLayoutBlank

Answer 1

问题来自于我们无法预测粘贴操作将持续多长时间以及何时完成。我们需要等待它完成。

' first let us count the shapes in the slide
Dim shapeCount As Integer: shapeCount = activeSlide.Shapes.Count
myPP.CommandBars.ExecuteMso "PasteExcelTableSourceFormatting"

Do '<~~ wait completion of paste operation
    DoEvents
Loop Until activeSlide.Shapes.Count > shapeCount

' Now, our table is the last in the shapes collection.
With activeSlide.Shapes(activeSlide.Shapes.Count)
    .Left = 23
    .Top = 105
    .Width = 650
    .Height = 375
End With