复制工作表类的方法失败

Question

我在PPT中有一个小程序，可以在Excel中制作表格的副本。它在Excel文件打开时工作；但是，如果我关闭Excel文件并在PPT中运行程序，它会显示运行时错误( worksheet类的复制方法失败)

Dim OWB As Excel.Workbook
Set OWB = GetObject(ActivePresentation.Path & "\Test.xlsx")
OWB.Sheets(1).Copy after:=OWB.Sheets(1)

当Excel文件" Test“打开时，我在PPT中运行它没有问题；如果我关闭Test excel，这个代码会给我这个错误。

任何帮助都将不胜感激！

Answer 1

您可以使用On Error Resume Next和On Error Goto 0来测试它是否像这样打开：

Public Sub test()
  Dim OWB As Excel.Workbook
  On Error Resume Next
    Set OWB = GetObject(ActivePresentation.Path & "\Test.xlsx")
  On Error GoTo 0
  If OWB Is Nothing Then
    MsgBox "File is not open", vbCritical
  Else
    OWB.WorkSheets(1).Copy after:=OWB.Sheets(1)
  End If
End Sub

或者像这样，如果你想让它为你打开：

Public Sub test()
  Dim OWB As Excel.Workbook
  Dim sFile As String
  sFile = ActivePresentation.Path & "\Test.xlsx"
  On Error Resume Next
    Set OWB = GetObject(sFile)
  On Error GoTo 0
  If OWB Is Nothing Then
    On Error Resume Next
      Set OWB = Excel.Application.Workbooks.Open(sFile)
    On Error GoTo 0
  End If
  If OWB Is Nothing Then
    MsgBox "Could not locate file to open", vbCritical
  Else
    OWB.WorkSheets(1).Copy after:=OWB.Sheets(1)
  End If
End Sub