Embedded hyperlinks to shared OneDrive folders are not working for other users.

[Strugglin Exceller]

My company uses OneDrive for all of our files. We use Excel heavily. We have a log file that logs links to other Excel files for easy access. But if I create the link, other users can't open that link even if the folders and files are shared. If someone else creates the link, I can't open it. I assume it's because the path is to my local folder where OneDrive is downloading the files too. But I would think that is the purpose of one drive. So all can access this. It appears however that Links are user-specific. I want it so others in my company can click on that link and open that file. Is there a solution to this problem?

 

[Artik]

If you can create links using the sheet function HYPERLINK, then you can use this solution.
1. In the standard module, paste the following function:

Function OneDriveFolder() As String
    Dim sPath

    sPath = Environ$("OneDriveCommercial") & "\"  'or ("OneDrive")   ' or ("OneDriveConsumer")
    OneDriveFolder = sPath
End Function

2. In the sheet you will use this way:

HYPERLINK(OneDriveFolder()&"Folder1\SubFolder\Produktion1.xlsx")

Although all users will see the account of the user who defined the HYPERLINK function (e.g. C:\Users\Artik\OneDrive - CompanyName\..., the link should work for everyone.


If you want to create links by inserting a link (from the context menu), this will require a bit of customization.
1. You create a link that references the same cell, and in the "Display text" field, enter the path to the file without the OneDrive root folder (e.g. Folder1\SubFolder\Produktion1.xlsx).
2. In the sheet module where you store document links, insert a procedure:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Me.Parent.FollowHyperlink OneDriveFolder() & Target.Range.Value
End Sub

3. In the standard module, use the OneDriveFolder function.

When you click on a link prepared this way, the _FollowHyperlink event will be raised. The event will build the path to the file.

Artik

 

[Strugglin Exceller]

If you can create links using the sheet function HYPERLINK, then you can use this solution.
1. In the standard module, paste the following function:

Function OneDriveFolder() As String
    Dim sPath

    sPath = Environ$("OneDriveCommercial") & "\"  'or ("OneDrive")   ' or ("OneDriveConsumer")
    OneDriveFolder = sPath
End Function

2. In the sheet you will use this way:

HYPERLINK(OneDriveFolder()&"Folder1\SubFolder\Produktion1.xlsx")

Although all users will see the account of the user who defined the HYPERLINK function (e.g. C:\Users\Artik\OneDrive - CompanyName\..., the link should work for everyone.


If you want to create links by inserting a link (from the context menu), this will require a bit of customization.
1. You create a link that references the same cell, and in the "Display text" field, enter the path to the file without the OneDrive root folder (e.g. Folder1\SubFolder\Produktion1.xlsx).
2. In the sheet module where you store document links, insert a procedure:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Me.Parent.FollowHyperlink OneDriveFolder() & Target.Range.Value
End Sub

3. In the standard module, use the OneDriveFolder function.

When you click on a link prepared this way, the _FollowHyperlink event will be raised. The event will build the path to the file.

Artik

Thank you... This is helpful.