CODEWITHSUNDEEP
vbaexcelpowerpoint
Pattt
Pattt

Reputation: 1

Copy method of worksheet class failed

I have a little program in PPT that makes copy of the sheets in Excel. It works when Excel file is open; however, if I close the Excel file and run the program in PPT, it says run time error (copy method of worksheet class failed)

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

I run this in PPT without a problem when the Excel file "Test" is open; If I close the Test excel, this code gives me this error.

Any help would be appreciated!

Upvotes: 0

Views: 335

Answers (1)

braX
braX

Reputation: 11735

You can use On Error Resume Next and On Error Goto 0 to test to see if it is open like this:

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

Or like this if you want it to open for you:

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

Upvotes: 1

Related Questions