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:

Sub ChangeHyperlinks()
Dim h As Hyperlink
Dim oldDr As String, newDr As String

oldDr = "T:\\servername"
newDr = "http:\\webaddress\"
For Each h In Sheets("Sheet1").Hyperlinks
h.Address = newDr & Mid(h.Address, Len(oldDr) + 1, Len(h.Address))
Next h
End Sub

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?

Use replace(string,look for,replace):

Sub ChangeHyperlinks()
Dim h As Hyperlink
Dim oldDr As String, newDr As String

oldDr = "T:\\servername"
newDr = "http:\\webaddress\"
For Each h In Sheets("Sheet1").Hyperlinks
h.Address = replace(h.address,oldDr,newDr)
Next h
End Sub

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?

I think maybe the drive would be T:\ not T:\\ so you need:

Sub ChangeHyperlinks()
Dim h As Hyperlink
Dim oldDr As String, newDr As String

oldDr = "T:\servername\"
newDr = "http:\\webaddress\"
For Each h In Sheets("Sheet1").Hyperlinks
h.Address = replace(h.address,oldDr,newDr)
Next h
End Sub

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

Sub ChangeHyperlinks()
Dim h As hyperlink
Dim oldDr As String, newDr As String

oldDr = "T:"
newDr = "https://xxxxxx.xxxxxxx.com/sites/xxxx/Design/Shared%20Documents/xxxxxx/"
For Each h In Sheets("Sheet1").Hyperlinks
h.Address = Replace(h.Address, oldDr, newDr)
Next h
End Sub

The original code posted works, so the replace function might need tweaking? thanks again

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)

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

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.

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

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:

h.address = "http://xxxx.xxx.xxxx/" & h.address

or

h.address = newDr & h.address