Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Change links in Excel (Excel2003 VBA)

    I have a workbook that is made with many hyperlinks in the cells that all use a mapped drive as first part of the path, e.g. a cell could have a link that looks like this:

    O:subfolder1file1.doc

    Hovering the mouse over the link then would should the full UNC path, e.g. sharemainsubfolder1file1.doc (if O: was mapped to sharemain)

    Most users can work with this as they all have the O:-drive mapped to the right share.


    Now I want to use this file also, but I have my O: drive mapped to something else. So when I look at the link, I get something like:

    mysharemymain1mymain2subfolder1file1.doc (if MY O: was mapped to mysharemymain1mymain2)


    I understand all of this. What I now want to program in VBA (if that is possible) however is the following behavior:

    (1) If the O: drive is mapped to the right share; exit, no changes (everything works as expected)

    (2) If the O: drive is either NOT mapped OR mapped to something else, CHANGE all hyperlinks (there are hundreds in several worksheets in my workbook) to use the UNC in-stead

    (I don't want the changes in (2) to be permanent but I can probably 'fix' that piece of the puzzle myself).

    Most easy & fast would be if the change in (2) could be done by changing one variable somehow (which would work if all hyperlinks would be built using the Excel HYPERLINK() function maybe???), but then the current sheet would have to be changed completely (editing all hyperlinks into a HYPERLINK() formula format).

    Anyway, before I dive into this one, I'd be interested to hear what is already done and what would NOT work. Any code that would help me re-invent the wheel would be appreciated as well.

    EJ

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Change links in Excel (Excel2003 VBA)

    Instead of changing all the hyperlinks, you could temporarily change the mapping of drive O:.
    You could create two batch files: one to map O: to the share needed for the workbook, and the other to map it back to the original share.

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change links in Excel (Excel2003 VBA)

    Yes, that's actually how a workaround I just discovered works. I don't like that as:

    (1) My O:-drive could be different from that of another visiting user, so the "map to the right share" batch file would work, but the "map back to where you came from" would be more difficult (but do-able, I agree)

    (2) While the sheet is open, my original O-drive is gone (I cannot work on that anymore and the un-mapping might fail if I have file open already)

    (3) If the sheet crashes (or my PC) the mapping is not restored (yes, I know I can fix that too).

    So I'd still like to keep mapping alone and change the hyperlinks somehow.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Change links in Excel (Excel2003 VBA)

    I'd place the UNC path in a cell and change the hyperlinks to HYPERLINK formulas. That would be a bit of work, but it would solve the problem once and for all, with the additional benefit that if your network structure changes in the future, you'd only have to change the value of the cell with the UNC path.

  5. #5
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change links in Excel (Excel2003 VBA)

    So then I'd have to find the UNC-path of a mapped drive, I can look around and will find it but if someone has that available...

    (yes, I'm lazy) <img src=/S/blackteeth.gif border=0 alt=blackteeth width=20 height=20>

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Change links in Excel (Excel2003 VBA)

    You can view the UNC path for a mapped drive in Windows Explorer. For a VBA solution, see for example <post#=469,933>post 469,933</post#> by JanB.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •