1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Hyperlink with Multiple Users

Discussion in 'Ask an Excel Question' started by DangerMan88, May 17, 2018.

  1. DangerMan88

    DangerMan88 New Member

    Messages:
    16
    Hello,

    I am trying to use a hyperlink on an icon that will open a folder location. The problem I am having is that the pathway is different depending on who is logged on to the computer. For example, using the pathway:

    C:\Users\dger\ShareFile\Shared Folders\VT_QA\Daniels Files\SQF\SQF 8.0\Food Safety\2.1 Management Commitment\2.1.1 Food Safety Policy

    Once another user is logged in though, C:\Users\dger will obviously be different.

    Is there a way for me to hyperlink a path like this so it works regardless of who is logged on?

    Regards,

    Dan G
  2. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,824
    Can't think of way to do it without VBA.

    If able to use VBA then just use ENVIRON("UserName") to get user name and use that to create hyperlink string and set it on the object.

    If you need further help, upload sample workbook.
  3. DangerMan88

    DangerMan88 New Member

    Messages:
    16
    Thanks for the quick response, I always love coming here to ask for help because it is such an active fantastic and supportive community.

    I will look this function up through an internet search and see if I can learn more about it and try to apply it. I am completely self taught in VBA and have been able to get what I need most of the time using it (sometimes with a bit of help), but by no means am I proficient. I will give it a shot this afternoon and let you know if I need help.

    Thanks.
  4. Haz

    Haz Member

    Messages:
    61
    Use relative paths.
    What's the path of the file containing the hyperlink?
  5. DangerMan88

    DangerMan88 New Member

    Messages:
    16
    C:\Users\dger\ShareFile\Shared Folders\VT_QA\Daniels Files\SQF\SQF 8.0
  6. DangerMan88

    DangerMan88 New Member

    Messages:
    16
    I have tried this with both ways in mind, and can't seem to get either to work. I've uploaded a sample of what I am trying to do. Basically each folder icon I want to link to a path that will just open a window to the location on the drive (not trying to open any specific file, just a path location).

    Regards,

    Dan G

    Attached Files:

  7. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,824
    Try below and assign it to Graphic22.
    Code (vb):
    Sub Graphic22_Click()
    ThisWorkbook.FollowHyperlink Address:="C:\Users\" & Environ$("UserName") & "\ShareFile\Shared Folders\VT_QA\Daniels Files\SQF\SQF 8.0\Food Safety\2.1 Management Commitment\2.1.1 Food Safety Policy", NewWindow:=True
    End Sub
    If above works. I'll work on getting you code that will generate dynamic hyperlink address for each folder shape.
  8. DangerMan88

    DangerMan88 New Member

    Messages:
    16
    I changed the hyperlink to a macro and used the code you gave me to try it out and it worked just fine. I tried it on another colleagues computer and it worked as well. Trying it on 2 other users computer's however, it did not work. One gave me a run time error, the other just gave me an error "Cannot open the file." I had enabled macros on both of their excels to make sure that wasn't the issue.
  9. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,824
    So, the issue is that folder structure isn't consistent across each user.

    Typically speaking, I wouldn't recommend using "ShareFile" from local machine to multiple users.

    Rather, file should be hosted on a server (NAS or some other storage server).

    Then, IT should enforce drive mapping via ADO policy (logon script) and map network-drive/shared folder to each computer specifying specific drive letter and path.

    This will ensure same folder structure/path for each shared folder and will ensure links etc work seamlessly across organization.

    Workaround is to have path and user name stored as lookup table in hidden sheet on the workbook. And use that in code to point hyperlink destination.
  10. DangerMan88

    DangerMan88 New Member

    Messages:
    16
    I believe the Citrix ShareFile is cloud based, not sure it is on local PC. I know this isn't a physical server, but would this type of storage be sufficient?
  11. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,824
    I've never worked with Citrix ShareFile. But how is path mapped?
    I'd imagine it's enforced via IT policy and not by end-user. Correct?

    If so, as long as same path structure is ensured on every end-user's terminal, it should work fine.

    Can you post the path for the folder of users that received error?

    EDIT: Oh and I assume everyone is using Windows machine. If not, well, you may be out of luck.
  12. DangerMan88

    DangerMan88 New Member

    Messages:
    16
    Yes, I believe the paths are mapped via IT, end-user cannot see what is on there unless they are given permissions and then they have to sync with ShareFile (similar to how you would sync with any cloud drive).

    So I reviewed it and her path was slightly different. Where my path is:

    'C:\Users\dger\ShareFile\Shared Folders\VT_QA\...',

    Her path is,

    'C:\Users\MPEL\ShareFile\Shared with Me\VT_QA\...'.

    Working with my IT person, we found that she was set up as a client in Citrix and never got a user account herself so anything she has been accessing had to be shared with her. He is going to add her as a user and we will try this again.

    Now to figure out why it isn't working on my manager's computer...

    Thanks again for all your help :)
  13. DangerMan88

    DangerMan88 New Member

    Messages:
    16
    Unfortunately our IT person is having issues fixing this, even tried completely reinstalling Citrix ShareFile to try and reset the paths to default, but it did not work. Not sure where to go from here. Can I use relative paths in VBA? I was trying this and had no such luck.
  14. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,824
    Relative path won't help you here. As folder structure isn't consistent. For relative path to work, you'll need same path structure from file location to destination. This is typically useful for network drives (mapped).

    Only workaround that I can think of right now, is to store individual path in hidden sheet with related user name. Do lookup operation in code to construct hyperlink destination.
  15. DangerMan88

    DangerMan88 New Member

    Messages:
    16
    I was able to figure this out with relative paths since the base file is within the folder called 'SQF 8.0'. This made it so I could ignore the entire first half of the pathway and only worry about descending folders from there. It works great on all of my coworkers computers now. Thank you all again for the help.

    The original code suggested was:
    Code (vb):
    Sub Graphic22_Click()
    ThisWorkbook.FollowHyperlink Address:="C:\Users\" & Environ$("UserName") & "\ShareFile\Shared Folders\VT_QA\Daniels Files\SQF\SQF 8.0\Food Safety\2.1 Management Commitment\2.1.1 Food Safety Policy", NewWindow:=True
    EndSub
    And this worked great except for the paths for some users not being exactly the same due to version and privilege differences in the ShareFile. Using relative paths as suggested by Haz, the new code for the hyperlink is:
    Code (vb):
     Sub Graphic22_Click()
    ThisWorkbook.FollowHyperlink Address:= ThisWorkbook.Path & "\Food Safety\2.1 Management Commitment\2.1.1 Food Safety Policy", NewWindow:=True
    EndSub

Share This Page