Hyperlink Panic ATTACK

Hi there amigos

I recently created a large (for my standards) database. Every file name is linked via hyperlink to the PDF file it represents so that when u click on it it opens the newspaper, circular, Agenda etc in Acrobat...

Well all these files were stored on CD so the hyperlin address started with E:\. On other computers tho CD Drive is commonly named D:\ and when I open the Excel spreadsheet there, none of the links work.

In my search for a solution I found out that if I just ommit the drive letter from the Hyperlink path and just enter it in File\Properties\Summary\hyperlink it works just fine.

But its a complete nightmare going through every single file changing the hyperlink paths...Any suggestions???


Thanks in advance

Hello Steven,

Add a Standard Module to your VBA project then copy and paste the macro code below into it. The macro "CleanLinks" will remove the drive letter from the hyperlink automatically. It cleans whichever worksheet is active.

Sub CleanLinks()

  Dim Addx As String
  Dim Colon As Long
  Dim Link As Hyperlink
  
    For Each Link In ActiveSheet.Hyperlinks
      Addx = Link.Address
      Colon = InStr(1, Addx, ":")
        If Colon > 1 Then
           Addx = Mid(Addx, Colon + 2, Len(Addx) - Colon - 1)
           Link.Address = Addx
        End If
    Next Link
    
End Sub

Sincerely,
Leith Ross

You sir, are a gentleman



Thanks