Excel File; CanCheckOut <> True in SharePoint

[ryguy7272]

Hello everyone. I’m using the following code to open an Excel file
from my SharePoint site.

If Workbooks.CanCheckOut(xlFile) = True Then
Application.EnableEvents = False
Workbooks.CheckOut xlFile
Application.EnableEvents = True
Application.StatusBar = "Opening file"
Set xlApp = New Excel.Application
xlApp.Visible = True
Set wb = xlApp.Workbooks.Open(xlFile, , False)
Application.StatusBar = ""
ThisWorkbook.Activate
Application.StatusBar = ""
End If

If the file is there, the code works fine. If the file is NOT there,
I get this message:
‘Run time error 1004’
Path . . . filename.xls could not be found. Check the spelling of the
name and verify that the location of the file is correct.

Well, I know the file is not there, so I certainly wouldn’t expect the
CanCheckOut status to be true, but how can I handle this error and
send the code to a sub named ‘CreateFile’ which can easily create the
file and name it and save it into SharePoint. The only problem is
that I don’t know how to handle this error. I tried some error
handling; got some ideas here:
http://www.cpearson.com/excel/ErrorHandling.htm

However, I didn’t get anything working yet, and I don’t know if I
really want to be throwing errors anyway. Is there a more eloquent
way of handling this?

Thanks so much!!
Ryan---

 

[Alan]

Hello everyone.  I’m using the following code to open an Excel file
from my SharePoint site.

    If Workbooks.CanCheckOut(xlFile) = True Then
        Application.EnableEvents = False
        Workbooks.CheckOut xlFile
        Application.EnableEvents = True
        Application.StatusBar = "Opening file"
        Set xlApp = New Excel.Application
        xlApp.Visible = True
        Set wb = xlApp.Workbooks.Open(xlFile, , False)
        Application.StatusBar = ""
        ThisWorkbook.Activate
        Application.StatusBar = ""
    End If

If the file is there, the code works fine.  If the file is NOT there,
I get this message:
‘Run time error 1004’
Path . . . filename.xls could not be found. Check the spelling of the
name and verify that the location of the file is correct.

Well, I know the file is not there, so I certainly wouldn’t expect the
CanCheckOut status to be true, but how can I handle this error and
send the code to a sub named ‘CreateFile’ which can easily create the
file and name it and save it into SharePoint.  The only problem is
that I don’t know how to handle this error.  I tried some error
handling; got some ideas here:http://www.cpearson.com/excel/ErrorHandling..htm

However, I didn’t get anything working yet, and I don’t know if I
really want to be throwing errors anyway.  Is there a more eloquent
way of handling this?

Thanks so much!!
Ryan---

Sub SubName

On Error GoTo MissingFile
If Workbooks.CanCheckOut(xlFile) = True Then
Application.EnableEvents = False
Workbooks.CheckOut xlFile
Application.EnableEvents = True
Application.StatusBar = "Opening file"
Set xlApp = New Excel.Application
xlApp.Visible = True
Set wb = xlApp.Workbooks.Open(xlFile, , False)
Application.StatusBar = ""
ThisWorkbook.Activate
Application.StatusBar = ""
End If
On Error GoTo 0
Exit Sub

MissingFile:
Resume CreateNewFile
CreateNewFile:
On Error GoTo 0
Call CreateFile
end Sub