Macro to create hyperlinks in

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:46 PM GMT

Question

I have anspreadsheet with about 2700 rows. Column A is made of file names (File A, File B, etc.) and each file is a link to the file with that name. The problem is that the directory of the file they link to has changed locations (i.e., "..\DirectoryA\FileA" is now "..\DirectoryB\FileA"). I am not good at VB and the macro I first tried replaced the entire link including the file name ("..\DirectoryB\FileA" for all links). How can I do this with a quick macro?

Answer

Steve,

- just an added comment in response to your comments in the rating. I use xl2007 where I wrote it and it worked for me in xl2007 - so I can't explain you problem there - maybe the values of S1 and S2 didn't exactly match the hyperlinks values in your test environment.

this worked fine for me:

Sub FixLinks() Dim s1 As String, s2 As String Dim sAddr As String, sSubAddr As String Dim s As String, hLink As Hyperlink, iloc1 As Long Dim iloc2 As Long s1 = "DirectoryA" s2 = "DirectoryB"

For Each hLink In ActiveSheet.Hyperlinks

 sAddr = hLink.Address
 sSubAddr = hLink.SubAddress
 iloc1 = InStr(1, sAddr, s1, vbTextCompare)
 iloc2 = InStr(1, sSubAddr, s1, vbTextCompare)
 If iloc1 > 0 Then
    s = Mid(sAddr, iloc1, Len(s1))
    sAddr = Replace(sAddr, s, s2)
    hLink.Address = sAddr
 End If
 If iloc2 > 0 Then
    s = Mid(sSubAddr, iloc2, Len(s1))
    sSubAddr = Replace(sSubAddr, s, s2)
    hLink.SubAddress = sSubAddr
 End If

Next End Sub

Note I didn't do anything with the display text of the hyperlink (what you see in the cell). If you have the path in the display text, then it won't change, but that has no affect on the hyperlink itself. The underlying address is changed.

Advertisement

©2017 eLuminary LLC. All rights reserved.