Intermittent RTE 1004 [MyFileName] could not be found (SharePoint)

[AndyPandy27]

Hi All,

I've been working on a utility to be rolled-out to a number of users, which requires either downloading from or uploading to an Excel document stored on our company's SharePoint.

After extensive testing on Friday, everything was working great; but upon booting up my machine this morning, when attempting to access this SharePoint document, I am getting the Run-time error 1004 MyFileName could not be found. I know it definitely exists though.

I did some testing again, and it transpires that there is something going on, which I don't fully understand - I'm hoping someone who knows these things more fully might be able to explain this, so I can figure out a fix:

Scenario 1:
ACTION: I boot up from laptop from it being off and attempt to run my code.
RESULT: Run-time error 1004 - MyFileName could not be found.

Scenario 2:
ACTION: After Scenario 1, I then manually access the SharePoint Site (this requires me to log on to our company VPN network) and open the document in question.
RESULT: The document definitely exists; everything is fine and I can open the document.

Scenario 3:
ACTION: I close the manually opened document, then try running my VBA code (still logged on to the company VPN network).
RESULT: Everything works perfectly.

Scenario 4:
ACTION: I log out of the company VPN network entirely, and try running my VBA code.
RESULT: Everything works perfectly.

Scenario 5:
ACTION: I clear all cookies/history etc from my web browsers (in an effort to mimic the action of turning off my computer, to recreate Scenario 1, without actually turning off the laptop). I then try running my VBA code again.
RESULT: Run-time error 1004 - MyFileName could not be found.

Scenario 6:
ACTION: Immediately after Scenario 5, log in to the company VPN network, but do *not* manually access the SharePoint site, instead try running my VBA code.
RESULT: Run-time error 1004 - MyFileName could not be found.

Initially, I thought perhaps I needed to be logged into the company network; but given how Scenario 4 and Scenario 6 worked out, I now think that somehow Excel is remembering something, which allows it to know the file location does exist, even when not logged in.

Has anyone got any experience of how SharePoint works, and whether or not I can get Excel to know this file can be found, without the need to manually access it first? I don't want users to

My code is below, with the highlighted line causing the error:

Sub MyTest()
    Dim MyPath As String
    Dim MyWb As Workbook
    MyPath = "https://companyname-my.sharepoint.com/personal/my_name/Documents/Folder%20Name/Document%20Name.xlsb"
[COLOR=#ff0000][B]    If Workbooks.CanCheckOut(MyPath) Then[/B][/COLOR]
        Workbooks.CheckOut (MyPath)
        Set MyWb = Workbooks.Open(MyPath)

Thanks,

AP

 

[AndyPandy27]

Hi Guys,

I've been continuing to investigate this, and in case it's of any use; a couple of additional things have cropped up:

1) It transpires that logging into SharePoint alone will *not* make the VBA code work; I definitely need to actually open the document in question manually before the VBA code works.

2) Once I have manually opened the document in SharePoint once, the VBA code will always work*, regardless of:
- Whether or not I am logged into the company VPN network
- Whether or not I am logged into SharePoint
- Whether or not I clear all cookies/browser history etc

*The caveat to point 2 above is that Excel remains open. Once I close Excel, until I open that SharePoint document again, the VBA will *not* work.

There must be a setting or cache or something storing a piece of information which is generated when the SharePoint document is opened, to confirm the document can be opened/checked out again etc...

If anyone can help, that would be great.

Cheers,

AP