Cross-platform VBA Function to get the OneDrive/SharePoint Url path/link from a local path of a locally synced folder

GetOneDriveUrlPath.bas.vb

' Cross-platform VBA Function to get the OneDrive/SharePoint Url path (link)
' from a local path of a locally synced folder (Works on Windows and on macOS)
'
' The functionality previously provided in this gist is now available in the
' 'GetRemotePath function from the following gist: 
' https://gist.github.com/guwidoe/038398b6be1b16c458365716a921814d

' Please use that instead!

Thanks for your work.

Any chance you could rewrite this as "proper" VBA, with End If statements and without colons and underscores everywhere? It might save a couple of lines, but makes the code hard to read and error-prone.

Hi @JefUtb, thanks for the comment!

You are right that the coding style I follow here is dubious and in many places I overdid it with the line continuations etc...

The reason I implemented it like this and why I sometimes adopt such a style in VBA is, that the Microsoft Office VBA IDE makes it very annoying to navigate larger projects.
Therefore, I like to write utility functions without any dependencies. I can put them all into one module and just copy-paste the ones I need into one module again, avoiding too many "library" modules that clutter the IDE.

Now the thing is, to actually rewrite this in a more readable way the most important change would be splitting it up into multiple procedures/functions, which I'm not too excited to do for the reasons I just explained. Many of the procedures I'd split this up into would have no other use than for this function (and maybe for GetLocalPath), so in my projects, I would have to always copy a bunch of interdependent functions or an entire library module, both of which I don't like.

I have spent a lot of time with this code and find it actually quite readable, stretching it out over more lines would make this function harder to navigate for me. Often when using line continuation, I try to shorten a block of code for which I know what it does and use multiple times (usually something to outsource into a different procedure), e.g.:

On Error Resume Next: cliPolColl fileName: keyExists = _
(Err.Number = 0): On Error GoTo -1: On Error GoTo 0

This just checks if a key already exists in a collection. I used to use Scripting.Dictionary instead but had to drop it for Mac compatibility.

fileNum = FreeFile
Open wDir & cid & ".dat" For Binary Access Read As #fileNum
    ReDim b(0 To LOF(fileNum)): Get fileNum, , b: s = b: size = LenB(s)
Close #fileNum: fileNum = 0

This just reads a file into the variable b as a byte array.

I use _ because I strictly adhere to an 80-character line-length limit in this code. This enables multiple editor windows side by side without horizontal scrolling. This enhances readability in my opinion.

I sometimes avoid End If by using

If condition Then _
    statement

to save a line. In my opinion, readability doesn't suffer because the block is still clearly indicated through indentation. Of course, it takes some getting used to it.

TL;DR:
This is supposed to be a copy-paste and everything-just-works-without-any-dependencies solution.

The friend I collaborated with on this solution implemented it independently in a library module and in a more conventional manner. The solution approach is the same but you may find it easier to read: link

The only difference is that he didn't implement Mac compatibility yet.

Hi, i use your awesome function and encountered an issue a few days ago that it doesn't give me the converted path anymore. but instead the https url.
What has happened, and how do i solve this :)
i use this one on stack overflow i might add.
https://stackoverflow.com/questions/77514627/save-email-attachment-to-sharepoint-onedrive/77517760?noredirect=1#comment136664713_77517760
In the top post where i explain my issue.

Hi @Bowman99, just to clarify: You have an HTTPS URL and you want to convert it to a local path? Then you are commenting under the wrong gist. 😊
Please try updating your function to the latest version which you can find here: https://gist.github.com/guwidoe/038398b6be1b16c458365716a921814d

If you still have the same issue afterwards let me know!

Hi buddy! Thanks for answering, yes you are right!
I'll try to update to the new code and ill get back to you.
Cheers!

Oh my god i love you :) It worked to update the code. I should’ve seen that explanation in the code sorry. I’ll look better next time!
Thank you so much for this, and your code over all guwidoe!

Hi @guwidoe,

I have run into a similar error that was found on GetLocalOneDrivePath.bas.vb

The debugger is shooting up error 457, “This key is already associated with an element of this collection”, at line 1164:
locToWebColl.Add VBA.Array(locRoot, webRoot, email, _
mainSyncID, mainSyncFind, dirName), locRoot

This has since been fixed but has the same bug been applied here?

Appreciate the help!

Hi @guwidoe,

I have run into a similar error that was found on GetLocalOneDrivePath.bas.vb

The debugger is shooting up error 457, “This key is already associated with an element of this collection”, at line 1164: locToWebColl.Add VBA.Array(locRoot, webRoot, email, _ mainSyncID, mainSyncFind, dirName), locRoot

This has since been fixed but has the same bug been applied here?

Appreciate the help!

Have you tried the last version?

yes, currently running the latest Updated: 2023/10/02

Hi @BlakeR94, thanks for the notice... You are right, not all bugfixes are implemented here. I will update the function today and let you know once its done!

Awesome, Thank you!

@BlakeR94, I have now updated the function, sorry for the delay!

Working perfectly, thank you!

Hi @guwidoe,

I am encountering the same error as BlakeR94: "Run-Time error '457': The key is already associated with an element of this collection."

It's on line 5530 of my code...

5530 locToWebColl.Add VBA.Array(locRoot, webRoot & "/" & cid, email, _ syncID, syncFind, dirName), Key:=locRoot

Not entirely sure why this would error here?

Thanks!
Tom

Hi @monitom and sorry for the late reply.
There seem to have been some OneDrive updates and I'll have to update this function accordingly.
Can you check if the GetRemotePath function from the current version of LibFileTools works on your machine?

Hi @monitom and sorry for the late reply. There seem to have been some OneDrive updates and I'll have to update this function accordingly. Can you check if the GetRemotePath function from the current version of LibFileTools works on your machine?

Hi @guwidoe - No worries at all. Thanks for your reply.

GetRemotePath in LibFileTools worked correctly for my purposes.