Excel 2003 Hyperlinking path problem

Hello,

I have a problem with hyperlinking within an Excel 2003 document. The properties of the hyperlink seem to change when the document is saved. So instead of the q:\document path\document name, it changes to ../../document name. Every time the document is saved it seems to add an additional ../ to the front of the document path. This results in the hyperlink becoming invalid.

Things we have tried to resolve this …

Set the base URL path to Q

Other things to consider

The hyperlinks are to other word and excel documents on the base Q drive and also to documents stored on S & T drives. The mapped drives are the same for everyone.

We are mapped to the root of the share, not a subfolder (I've seent the MS document about that)

The document is password protected and only 2 people can save this document. They both run Excel 2003.

There are thousands of hyperlinks in the document. I’m aware that using the =hyperlink or manually typing in \\servername\sharename\documentpath would change the default save behavior and it would not then alter the links but the work involved would be prohibitive.

Could you offer some advice on the best way forward for fixing the existing problem and preventing it from reoccurring.

Many thanks.
LVL 1
tribzAsked:
Who is Participating?
 
tribzConnect With a Mentor Author Commented:
Whilst the suggestions where helpful, it was actually resolved by bringing all clients up to 2007 level of Excel. We discovered that there was an additional 2007 user who was making edits to this spreadsheet. After they had done this, the 2003 users had problems with the hyperlinks as per above. The solution was to bring everyone up to the same level of 2007.

Thank you for all of your suggestions
0
 
gbanikCommented:
Put this code in the Worksheet Open event. This replaces the "../" address with your predefined server path everytime the workbook opens.
Private Const sBasePath As String = "" '"\\server\folder\"
Private Const sBaseSheet As String = "Sheet1"

Private Sub Workbook_Open()
Dim oSheet As Worksheet, oLink As Hyperlink, sNewLink As String
Set oSheet = Application.Sheets(sBaseSheet)
For Each oLink In oSheet.Hyperlinks
    If Left(oLink.Address, 3) = "../" Then
        sNewLink = Replace(oLink.Address, "/", "\")
        sNewLink = Mid(sNewLink, InStrRev(sNewLink, "\") + 1)
        oLink.Address = sBasePath & sNewLink
    End If
Next
End Sub

Open in new window

0
 
gbanikCommented:
Private Const sBasePath As String = "" '"\\server\folder\"
=
Private Const sBasePath As String = "\\servername\sharename\documentpath\"

ensure the "\" in the end.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
tribzAuthor Commented:
Thanks for the replies. Would I be right in thinking this is a programming 'find and replace'? If so, would it cope with not all documents being on the Q drive (some are on S & T).

Thanks
Jon
0
 
gbanikCommented:
If that is the case what you could do is, put the actual link addresses in the column next to the links and hide the column. Then use the following code to ensure that the addresses are remapped if needed.

Private Const sBaseSheet As String = "Sheet1"

Private Sub Workbook_Open()
Dim oSheet As Worksheet, oLink As Hyperlink
Set oSheet = Application.Sheets(sBaseSheet)
For Each oLink In oSheet.Hyperlinks
    If Left(oLink.Address, 3) = "../" Then
        oLink.Address = oLink.Parent.Offset(, 1).Value
    End If
Next
End Sub
0
 
gbanikCommented:
or the following code if you want the folder name only to be kept in the hidden column... the complete file name for the hyperlink would be recreated from the ../.. link and the NEXT column information

Private Const sBaseSheet As String = "Sheet1"

Private Sub Workbook_Open()
Dim oSheet As Worksheet, oLink As Hyperlink, sNewLink As String
Set oSheet = Application.Sheets(sBaseSheet)
For Each oLink In oSheet.Hyperlinks
    If Left(oLink.Address, 3) = "../" Then
        sNewLink = Replace(oLink.Address, "/", "\")
        sNewLink = Mid(sNewLink, InStrRev(sNewLink, "\") + 1)
        oLink.Address = oLink.Parent.Offset(, 1).Value & "\" & sNewLink
    End If
Next
End Sub
0
 
gbanikCommented:
Did your query get resolved?
0
 
tribzAuthor Commented:
If I'm honest, this is above my level of expertise with Excel. I'm going to backup the system and then try the above suggestions. I will first need to learn how to implement the above into a worksheet so I'll spend some time Googling it.

Thanks,
0
 
gbanikCommented:
I have worked out a sample sheet with code for you. The code gets fired automatically when u open the sheet (with macros enabled). The links could be any number and anywhere on the sheet, just ensure that the actual folder paths are entered in the adjacent column respectively.

I have added a msgbox just to illustrate. It can be removed later. Also as mentioned earlier, the folder data column can be kept hidden.
RefreshLinks.xls
0
 
tribzAuthor Commented:
Thanks, I'll have a go with this today!
0
 
tribzAuthor Commented:
Ah, very good! Could you let me know how I access the code? Sorry if this is a simple question, my knowledge isnt good at this level.
0
 
tribzAuthor Commented:
Could I also ask why this hyperlinking problem comes about?
0
 
gbanikCommented:
First, u dont have to access the codes. The codes would fire automatically when the Workbook is opened everytime. Just ensure that the macros are enabled and the link information is kept in the adjacent column.

Second, this happens because of the way Excel stores its links. It stores the relative paths rather than the absolute ones. There also may be a small glitch in Excel in this area the way it converts and keeps the links. I have noticed this many times. So, a sure workaround is to recreate the links (if you have many in your worksheet).
0
 
tribzAuthor Commented:
as per comment above
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.