PowerPoint VBA在运行期间无法识别Excel表 [英] PowerPoint VBA does not recognize Excel table during run
问题描述
我有一段很不错的代码,可以从excel文件中复制一个范围并将其粘贴到PowerPoint中的activeslide上.经过数小时的尝试,将excel的范围粘贴为表格(没有图像),我发现以下代码可以成功工作.注意:myPP = PowerPoint应用程序.
I have a peice of code which copies a range from my excel file and pastes it onto my activeslide in PowerPoint. After hours of attempts to get the range from excel pasted as a table (NOT an image), I found the below code to work successfully. Note: myPP = powerpoint application.
myPP.CommandBars.ExecuteMso "PasteExcelTableSourceFormatting"
myPP.CommandBars.ReleaseFocus
问题在于,当我执行宏时,将粘贴表,但是除非逐步执行代码,否则vba无法识别该表.我已经使用以下代码对此进行了测试.在执行过程中,代码将被完全跳过,但在逐步执行过程中将被触发.
The problem is that when I execute the macro, the table is pasted, but vba does not recognize the table unless the code is stepped through. I've tested this with the below code. During execution the code is skipped entirely, but during step-through it triggers.
For Each shp In activeSlide.Shapes
If shp.HasTable Then
MsgBox shp.Name
End If
Next
下面是完整代码.基本上,我只是希望将excel范围作为表格粘贴到我的Powerpoint中,并希望将该表格扩展为适合幻灯片.我愿意提出一些修改建议.谢谢您的帮助
Below is the full code. Basically I just want the excel range to be pasted into my powerpoint as a table, and for that table to be expanded to fit the slide. I'm open to suggestions in revising it a bit. Thanks for your help
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
推荐答案
问题来自以下事实:我们无法预测粘贴操作将持续多长时间以及何时结束.我们需要等待其完成.
The problem comes from the fact that we cannot predict how much time the paste operation will last, and when it finishes. We need to wait for its completion.
' 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
这篇关于PowerPoint VBA在运行期间无法识别Excel表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!