+ Reply to Thread
Results 1 to 8 of 8

editing hyperlinks using vba?

  1. #1
    Registered User
    Join Date
    06-03-2009
    Location
    Mountain View, CA
    MS-Off Ver
    Excel 2003
    Posts
    46

    editing hyperlinks using vba?

    Hi All,

    I made the mistake of creating hyperlinks to files on a server (using the \\SERVERNAME convention) without using the "hyperlink base" option.

    I've been sufficiently chastened, but the present problem is that I have many such hyperlinks that are now worthless because, when saved, they point to "../../[foldername1]/[foldername2]/[filename]", etc.

    Can anyone help me with a macro that would replace the first five characters ("../..") of all the hyperlinks on my sheet with //[MYSERVERNAME] ?

    Or is there a way to do this w/o having to program?

    Thanks in advance
    Last edited by prawer; 09-10-2009 at 04:25 PM.

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: editing hyperlinks using vba?

    Hello prawer,

    The answer depends on how the hyperlinks were created. If they created using any other language than VBA then probably not. If created using the Excel formula "=HYPERLINK", absolutely not.

  3. #3
    Registered User
    Join Date
    06-03-2009
    Location
    Mountain View, CA
    MS-Off Ver
    Excel 2003
    Posts
    46

    Re: editing hyperlinks using vba?

    hmmm .... that's interesting. I have virtually 0 experience with using vba in excel but a bit of general familiarity with programming concepts, so I'm somewhat surprised.

    The links were created simply by writing text in cells, right clicking -> hyperlink -> from recently browsed files -> select and 'OK'.

    I guess I'd assumed that there would be some way to read the hyperlink as a string, remove the five left-most characters and assign the new string as the hyperlink attached to the contents of the cell in question (and then scrub rinse repeat for all other cells containing hyperlinks, if necessary).

    This page seems to imply that it's at least possible to 'fetch ' a hyperlink (it creates a UDF to do it):

    Please Login or Register  to view this content.
    On a related vein, I'm wondering if this is a symptom of a compatibility issue -- when I save using Excel 2003 the link gets corrupted (my server name is replaced with "../../"), but that does not happen using Excel 2007.

    In any case, thanks for your help ...

  4. #4
    Registered User
    Join Date
    06-03-2009
    Location
    Mountain View, CA
    MS-Off Ver
    Excel 2003
    Posts
    46

    Re: editing hyperlinks using vba?

    I really wish I was fundamentally more strong with vba because I keep finding hints on the net that this is possible. Another site demonstrates an alternative approach for fetching URLs from cells using a UDF:

    Please Login or Register  to view this content.
    So it looks all I'd have to do is write a macro which takes a similar approach -- takes what the macro author here calls ST1, chop off the first five characters, and set the new ST1 as the hyperlink of the active cell. I just don't know how to put it into action ... any ideas?

  5. #5
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: editing hyperlinks using vba?

    Can you post a sanitized workbook that illustrates?
    Entia non sunt multiplicanda sine necessitate

  6. #6
    Registered User
    Join Date
    06-03-2009
    Location
    Mountain View, CA
    MS-Off Ver
    Excel 2003
    Posts
    46

    Re: editing hyperlinks using vba?

    shg --

    thanks for the suggestion. in the attached, my server name in the hyperlinks in the 'file' field has been replaced with "../.."

    hope this is something folks can help with.

    thanks!
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    04-18-2009
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    267

    Re: editing hyperlinks using vba?

    Try using this code:

    Please Login or Register  to view this content.
    Since the code works on the selection, be sure to select all the hyperlinked cells before running the code.

    --Karan--

  8. #8
    Registered User
    Join Date
    06-03-2009
    Location
    Mountain View, CA
    MS-Off Ver
    Excel 2003
    Posts
    46

    Re: editing hyperlinks using vba?

    Brilliant! Thanks Karan

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1