EXCEL2010: VBA opening another Excel file from a sharepoint site

[gajtguy]

Hi folks. Hopefully someone can help me identify something that is causing me to go mad. I have an excel file on my local machine, with a VBA procedure that should open another Excel file from a secure Sharepoint site. Once opened, the program will do some simple stuff, but I can't even get this program to consistantly open the file. This Sharepoint site is something I need to login to. I've tried logging in, then running the program, or staying out of sharepoint completly.

The madness comes in to play as earlier this afternoon my program ran without any issues. It opened the file, and displayed the total # of rows in the 'usedrange' for a specific sheet, and then checked it back in. But then I closed everything down and ate some dinner and now it won't even open the file. I did try restarting the computer.

Current error: Run-time error '1004': file could not be found. Check the spelling and location.... I've copied/pasted the file path directly from the file (properties window), into my VBA program. Same issue.
The error occurs during the workbook.cancheckout portion of the program

Here is the code:

Private Sub testthis()
    ' Attempt to open a sharepoint file
    Dim oWB As Workbook, MyPath As String
    MyPath = "https://sharepoint.domain.com/folder1/folder2/folder%20number%20three/filename.xlsx"  ' Obviously this is just a sample path. My code has the proper path & filename
    If Workbooks.CanCheckOut(MyPath) = True Then
        MsgBox ("File on Sharepoint CAN be checked out.")
        Workbooks.CheckOut MyPath
        Set oWB = Workbooks.Open(MyPath)
        
    Else
        MsgBox ("File on Sharepoint can NOT be checked out." + Chr(13) + _
                "Make sure no one else is working in the file." + Chr(13) + _
                "Including yourself.")
        Exit Sub
    End If
    MsgBox (oWB.Worksheets("Specs-Undated-Small-Low%").UsedRange.Rows.Count)
    oWB.CheckIn (False)
    Set oWB = Nothing
End Sub

Any ideas? Is this proper for opening a file from sharepoint, or should I use some other method?
Thanks a bunch.

[gajtguy]

My workaround for this issue is this: Trap the error and tell the user to Checkout/checkin the file manually, then run the program again with the Sharepoint site still open (but the file is closed and checked back in).

But the question still remains why the Macro can't get to the Sharepoint site from the start. Of course there exist the possibility that I'm the idiot who thinks that should be possible in the first place.

[ThomasMcA]

Although this probably won't help the OP of a year-old question, it might help future Googlers:

The differences that work for me:
1) Replace the %20 in your URL with blanks.
2) In the CanCheckOut, I used this format:

If Workbooks.CanCheckOut(Filename:=strURL) Then