Modify hyperlink - insert text

[Norbury2]

Hi, I have a worksheet with lots of hyperlinks to files. I have moved all the files into a subfolder of their previous location called Archive. I want to edit all the hyperlinks to include "Archive" in them. They are relative hyperlinks. In my head this should be easy, but I can't work out a simple way to do it. All the hyperlinks are in one column (O), but not all the cells in the column have links.

Using Excel 2016 and VBA 7.1

 

[Kamolga]

Hi,

You can loop through the hyperlinks of the sheet. If they are all in the same folder, which is for example 15 caracters of the path, then you could use something like this

Sub newSub()
Dim sh As Worksheet: Set sh = ActiveSheet
Dim hypLink As Hyperlink
Dim Filepath1 As String
Dim Filepath2 As String
  If sh.Hyperlinks.Count > 0 Then
            For Each hypLink In sh.Hyperlinks
                Filepath1 = Left(hypLink.Range.Address, [COLOR=#ff0000]15[/COLOR])
                Filepath2 = Right(hypLink.Range.Address, Len(hypLink.Range.Address) - Len(Filepath1))
                hypLink.Range.Address = Filepath1 & "Archive/" & Filepath2
            Next hypLink
  End If
End Sub

 

[Norbury2]

Thank you, however when I try to run I get a compile error telling me "Assignment to constant not permitted"?

 

[Kamolga]

Sorry, no .range required. Actually "Replace" (I tried the macro below and it worked, just adapt the blue to your path) might be easier to handle than the amount of caracters:

Sub newSub()
Dim sh As Worksheet: Set sh = ActiveSheet
Dim hypLink As Hyperlink
  If sh.Hyperlinks.Count > 0 Then
            For Each hypLink In sh.Hyperlinks
                hypLink.Address = Replace(hypLink.Address, "[COLOR=#0000ff]C:\Initial File[/COLOR]", "[COLOR=#0000ff]C:\Initial File[/COLOR]\Archive")
            Next hypLink
  End If
End Sub

 

[Norbury2]

Thank you!

What I actually ran was this:

Sub ArchiveLink()
Dim sh As Worksheet: Set sh = ActiveSheet
Dim hypLink As Hyperlink
Dim Filepath As String
  If sh.Hyperlinks.Count > 0 Then
            For Each hypLink In sh.Hyperlinks
                Filepath = hypLink.Address
                hypLink.Address = "Archive\" & Filepath
            Next hypLink
  End If
End Sub

Worked like a charm. Probably didn't need the "If" given that I knew there were hyperlinks, but never mind.

 

[Kamolga]

Hi,

Perfect!
I don't understand how it works though: if your link was c:\document\book1.xls, with the macro you posted you would now get Archive\c:\document\book1.xls, no?

_{^{<strike>
</strike>}}​

 

[Norbury2]

The hyperlinks were relative links, not full address, so they just look at the portion that is different from the address of the current file. Made my life a lot easier in this case!