Is Workbook Stored in OneDrive Already Open?

[AndyPandy27]

Hi Guys,

I have created a Workbook which is due to be used by ~100 users.

Each user will have their own copy of this Workbook, which contains a Userform which allows them to update/add/remove a selection of records (these records are just rows in a worksheet which sits behind the Userform).

They will then be able to "Sync" these records to a Master Workbook.

The Master Workbook is stored on OneDrive (Microsoft's online storage solution, similar to Dropbox).

The "Sync" works by opening the Master Workbook and performing a simple copy/paste from the user's Workbook to the Master Workbook, then saving and closing the Master Workbook - nice and simple.

But for this idea to work, I need to be able to incorporate a check to determine if the Master Workbook is already open or not, e.g.:

Function IsFileOpen(MyFileName As String) As Boolean
    Dim ff As Long, ErrNo As Long
    On Error Resume Next
    ff = FreeFile()
    Open MyFileName For Input Lock Read As #ff
    Close ff
    ErrNo = Err
    On Error GoTo 0
    Select Case ErrNo
        Case 0:    IsFileOpen = False
        Case 70:   IsFileOpen = True
        Case Else: Error (ErrNo)
    End Select
End Function

However, none of the functions I have found seem to work correctly when using the URL based filepath I have for my OneDrive document.

The path for my document is:

"https://mycompanyname-my.sharepoint.com/personal/my_name_my-company_name/Documents/Folder Name/Document Name - National Version.xlsb"

Or the "URL Encoded" version:

"https://mycompanyname-my.sharepoint.com/personal/my_name_my-company_name/Documents/Folder%20Name/Document%20Name%20-%20National%20Version.xlsb"

I have changed the words in these paths (for privacy), but the syntax is identical, and the real path does not contain any illegal characters etc, just standard alphanumeric characters.

With the above code, using either of the above two filepaths results in Run-time error 52 (Bad file name or number). This error occurs regardless of whether or not the Workbook is actually open. (nb, using the above code with a locally saved Workbook works without an issue).

Has anyone found a solution to being able to check if a Workbook hosted online is already open, before attempting to execute code which will open/edit it?

Any help would be greatly appreciated - please let me know if you need any further details.

Thank you!

AP

 

[AndyPandy27]

*bump*

 

[AndyPandy27]

*bump*

 

[RedBeard]

Have you tried mapping the the URL as a network location?

 

[AndyPandy27]

Hi RedBeard,

Thank you for the suggestion - I appreciate your input. Yes, this would (and does work), as the path then changes from a URL to a local document...

However, as this Workbook is due to be used by lots of people (all of whom are not based in an office), it would be a very difficult task to organise installing the OneDrive add-in which allows you to map the URL as a network location (our IT dept is not adept at remote installations :-\).

If at all possible, to make things as easy and hassle-free as they can be - I would like the Workbook to be functional "as is", without the need for further instructions/installations etc.

It seems like I'm really close, there is just something which Excel doesn't like with the URL being passed as the filepath...

Thanks again though,

AP

 

[AndyPandy27]

I've continued to search for an answer - and managed to find the following code from Ivan Maola, however, this always results in the macro thinking the workbook is open, regardless of whether or not it actually is open:

Function IsMyFileOpen(MyFullPath As String) As Boolean
    Dim hdlFile As Long
    On Error GoTo FileIsOpen:
    hdlFile = FreeFile
    Open MyFullPath For Random Access Read Write Lock Read Write As hdlFile
    IsMyFileOpen = False
    Close hdlFile
    Exit Function
FileIsOpen:
    IsMyFileOpen = True
    Close hdlFile
End Function

As with the above, I've tried both URLs for my SharePoint document:

"https://mycompanyname-my.sharepoint.com/personal/my_name_my-company_name/Documents/Folder Name/Document Name - National Version.xlsb"

and

"https://mycompanyname-my.sharepoint.com/personal/my_name_my-company_name/Documents/Folder%20Name/Document%20Name%20-%20National%20Version.xlsb"

but neither works...

Does anyone know if I am referencing the URL path incorrectly, or if I need to add an additional reference into my references library for a URL path to work correctly?

Thanks again,

AP

 

[AndyPandy27]

*bump*