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?
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