我有一些相当简单的VBA代码,可以将Excel中每次复制1或2行内容到连续的PowerPoint幻灯片上。
在调试模式下逐行运行该代码时,代码能够完美执行。然而,当我不手动逐步运行它时,在while循环的最初阶段,通常是在第二或第三个迭代时,就会出现错误。
以下是该代码:
如前所述,当代码是“实时”运行时,即不逐步运行时,代码总是在以下任一行上失败。
我通常遇到的错误是这个:运行时错误 -2147023170: 自动化错误:远程过程调用失败。但有时我也会遇到运行时错误462,甚至是运行时错误-2147467259(对象'_CommandBars'的方法'ExecuteMso'失败)。
当我逐步执行代码时它能正常工作,这使我认为它可能与时间/进程优先级有关,但添加Application.Wait语句等待10秒并没有解决问题。
欢迎提供任何帮助!
在调试模式下逐行运行该代码时,代码能够完美执行。然而,当我不手动逐步运行它时,在while循环的最初阶段,通常是在第二或第三个迭代时,就会出现错误。
以下是该代码:
Private Sub CommandButtonExportToPowerPoint_Click()
Dim rng As Range
Dim PowerPointApp As Object
Dim myPresentation As Object
Dim mySlide As Object
Dim myShape As Object
Dim lFirstRow As Long
Dim lLastRow As Long
Dim sRangeString As String
Dim lNumberOfPptSlidesToAdd As Long
lFirstRow = 84
lLastRow = Cells(Rows.Count, 1).End(xlUp).Row
lNumberOfPptSlidesToAdd = (lLastRow - lFirstRow) / 2
sRangeString = "B" & lFirstRow & ":B" & lLastRow & ",L" & lFirstRow & ":L" & lLastRow & ",M" & lFirstRow & ":M" & lLastRow & ",N" & lFirstRow & ":N" & lLastRow
Set rng = ThisWorkbook.ActiveSheet.Range(sRangeString)
rng.Select
On Error Resume Next
Set PowerPointApp = GetObject(class:="PowerPoint.Application")
Err.Clear
If PowerPointApp Is Nothing Then Set PowerPointApp = CreateObject(class:="PowerPoint.Application")
If Err.Number = 429 Then
MsgBox "PowerPoint could not be found, aborting."
Exit Sub
End If
On Error GoTo 0
Application.ScreenUpdating = False
PowerPointApp.Visible = True
PowerPointApp.Activate
Set myPresentation = PowerPointApp.Presentations.Open("C:\some\path\to\existingppt\test.pptx")
rng.Copy
Sheets("Sheet1").Range("E1").PasteSpecial Paste:=xlPasteValues
Sheets("Sheet1").Range("E1").PasteSpecial Paste:=xlPasteFormats
Dim lCurrentFirstRowToCopy As Long
lCurrentFirstRowToCopy = 2 + 1
lLastRow = lLastRow - lFirstRow + 1
Dim lPowerPointCurrentSlide As Long
lPowerPointCurrentSlide = 18
Dim sFirstRowValue, sSecondRowValue As String
While lCurrentFirstRowToCopy <= lLastRow
If Sheets("Sheet1").Range("E" & lCurrentFirstRowToCopy).MergeCells = True Then
MsgBox ("Cell E" & lCurrentFirstRowToCopy & " is merged: " & Sheets("Sheet1").Range("E" & lCurrentFirstRowToCopy).MergeCells)
End If
sFirstRowValue = Sheets("Sheet1").Range("E" & lCurrentFirstRowToCopy).Value
sSecondRowValue = Sheets("Sheet1").Range("E" & lCurrentFirstRowToCopy + 1).Value
If Left(sFirstRowValue, 5) = Left(sSecondRowValue, 5) Then
Set rng = Sheets("Sheet1").Range("E" & lCurrentFirstRowToCopy & ":H" & lCurrentFirstRowToCopy + 1)
lCurrentFirstRowToCopy = lCurrentFirstRowToCopy + 2
Else
Set rng = Sheets("Sheet1").Range("E" & lCurrentFirstRowToCopy & ":H" & lCurrentFirstRowToCopy)
lCurrentFirstRowToCopy = lCurrentFirstRowToCopy + 1
End If
Application.CutCopyMode = True
rng.Copy
myPresentation.Slides(lPowerPointCurrentSlide).Select
PowerPointApp.CommandBars.ExecuteMso "Paste"
Application.CutCopyMode = False
lPowerPointCurrentSlide = lPowerPointCurrentSlide + 1
Wend
rng.Clear
End Sub
如前所述,当代码是“实时”运行时,即不逐步运行时,代码总是在以下任一行上失败。
myPresentation.Slides(lPowerPointCurrentSlide).Select
或行
PowerPointApp.CommandBars.ExecuteMso "Paste"
我通常遇到的错误是这个:运行时错误 -2147023170: 自动化错误:远程过程调用失败。但有时我也会遇到运行时错误462,甚至是运行时错误-2147467259(对象'_CommandBars'的方法'ExecuteMso'失败)。
当我逐步执行代码时它能正常工作,这使我认为它可能与时间/进程优先级有关,但添加Application.Wait语句等待10秒并没有解决问题。
欢迎提供任何帮助!