Username in Hyperlink

Question

Hello,

we have all our Folders in a Network Storage.

Now we wanna Move all the Data to MS Teams / MS Sharepoint, with a Link to One Drive so that we can interact with FileSystemObject in VBA to Move / Copy Folders.

Now i read out in a VBA the Application.Username and Edit it to Only the Last Name when i wanna interact with FileSystemObject and One Drive.

C:\Users$MyLastname\OneDrive - Company\EEG - LIS \PV\Street 123, $MyLastname is set as variable when the user hit the Macro / VBA

So far so good, but we have in Each Street Adress a Hyperlink to the Folder.

Now we have the Problem, that the Directory to the One Drive is with the Username in it e.g.:

C:\Users\MyLastname\OneDrive - Company\EEG - LIS \PV\Street 123

But when another user wanna open that Directory, there is my Name in the Hyperlink, so that doesnt work because his Directory is c:\user\MyLastnameB and not MyLastnameA

Is there a way to build a Hyperlink to the Folder which opens the Folder in Explorer from every user which has link the Sharepoint Folder with his One Drive?

Answer 1

Hi Sebastian,

I'm working on the same problem. I think Sukie has misunderstood the situation - Sukie, it's not that Sebastian's colleagues should be able to access the C:\Users\Sebastian... link, but that the link generated by onedrive+sharepoint automatically contains the user's name in the URI since OneDrive stores sharepoint-synced folders in the user's home folder.

The alternatives:

1. Use the full sharepoint address instead of the OneDrive synced address

This works for simple documents, for example read-only access to information.
However, you cannot edit files directly using this method - for example if you link .accdb files with this method, they are opened from the temporary internet objects folder (!!); any changes you make and save are written to the temp copy, and are lost when you close the file.

2. Update every directory link each time

Sebastian, in your example you already edit a path in VBA when the user runs a macro. You could update the directory URI for each directory link during the same macro.
However: if you have more than a handful of links, this is a non-option due to the amount of overhead.

3. Use relative links

The best method I have found so far is to use relative links. You can start a filepath with ./ to start a path in the same folder as the file that the macro is being run from. For example Sebastian if your VBA file is in the EEG-LIS folder, you use the path: ./PV/Street 123

You can also use ../ to go up a folder in the hierarchy; for example if your working file is in EEG-LIS/master/code/ then your path would be ../../PV/Street 123

The downside here is that you have to store your file with VBA code in the onedrive folder, otherwise the relative path still requires the username.

Answer 2

Dear Sebastian,

Good day, thanks for querying in this forum.

From your description, it seems that you are trying to make the others access the hyperlink which contains username.

If my understanding is right, we’re afraid that there is no out of box way to achieve your requirement.

As far as we know, only local user can access the local URL, for the other users, they cannot access the other user’s local URL.

We’re sorry that it doesn’t meet your requirement, we do understand the inconvenience caused and apologize for it.

At the same time, we will also keep this thread open to check if the other community members have any other suggestions.

If the scenario above is not consistent with yours, you can also post back and point that.

Your understanding and patience will be highly appreciated. I hope that you are keeping safe and well!

Sincerely,

Sukie | Microsoft Community Moderator