+ Reply to Thread
Results 1 to 10 of 10

Replace multiple hyperlinks

  1. #1
    Registered User
    Join Date
    05-30-2013
    Location
    london
    MS-Off Ver
    Excel 2010
    Posts
    5

    Question Replace multiple hyperlinks

    Hi,

    I'm using a macro to replace hyperlinks in bulk.

    e.g. the current hyperlinks are: T:\\servername\directory1\directory123\123workbook.xlsl

    I need to replace the hyperlink with a web address, so I need to get rid of "T:\\servername" and keep the remainder as the links are sequential. I'm using following macro (found on this forum), which almost works perfectly:

    Please Login or Register  to view this content.
    This works but it trims the first 3 characters from "directory123", I know this is down to the "Len(olddr) + 1" from above code but not sure how to fix. can anyone help?

  2. #2
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Replace multiple hyperlinks

    Use replace(string,look for,replace):
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    05-30-2013
    Location
    london
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Replace multiple hyperlinks

    Thanks for the reply, I've replaced the code with what you posted but it doesn't do anything at all, is there something I'm missing?

  4. #4
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Replace multiple hyperlinks

    I think maybe the drive would be T:\ not T:\\ so you need:
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    05-30-2013
    Location
    london
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Replace multiple hyperlinks

    tried that, didn't make difference, this is what I have:

    Please Login or Register  to view this content.
    The original code posted works, so the replace function might need tweaking? thanks again

  6. #6
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Replace multiple hyperlinks

    I checked the second bit of code I put and it worked ok. Can you upload an example workbook so I can go through the hyperlinks you have? (to upload an attachment click go advanced on the reply and then attachments)

  7. #7
    Registered User
    Join Date
    05-30-2013
    Location
    london
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Replace multiple hyperlinks

    I must be doing something wrong, I've attached a sample worksheet.
    thanks
    Attached Files Attached Files

  8. #8
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Replace multiple hyperlinks

    The addresses for your hyperlinks are:
    RFQ1237/RFQ1237.xls
    RFQ1238/RFQ1238.xls
    and so on, so it doesn't find "Y:" in the hyperlink so it does nothing.

  9. #9
    Registered User
    Join Date
    05-30-2013
    Location
    london
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Replace multiple hyperlinks

    interesting, you're right. When I hover over the links it does show Y: ...strange. In that case how would I append the newDR: web address? So I would want:

    http://xxxx.xxx.xxxx/RFQ1237/RFQ1237

  10. #10
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Replace multiple hyperlinks

    Possibly because you created the hyperlinks when the file was saved in the same folder so ignored the first part? I 'm not sure.

    You need:
    Please Login or Register  to view this content.
    or
    Please Login or Register  to view this content.

+ 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