Solved

Excel 2003 Hyperlinking path problem

Posted on 2010-11-10
15
501 Views
Last Modified: 2012-08-13
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.
0
Comment
Question by:tribz
  • 7
  • 7
15 Comments
 
LVL 13

Expert Comment

by:gbanik
Comment Utility
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
 
LVL 13

Expert Comment

by:gbanik
Comment Utility
Private Const sBasePath As String = "" '"\\server\folder\"
=
Private Const sBasePath As String = "\\servername\sharename\documentpath\"

ensure the "\" in the end.
0
 
LVL 1

Author Comment

by:tribz
Comment Utility
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
 
LVL 13

Expert Comment

by:gbanik
Comment Utility
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
 
LVL 13

Expert Comment

by:gbanik
Comment Utility
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
 
LVL 13

Expert Comment

by:gbanik
Comment Utility
Did your query get resolved?
0
 
LVL 1

Author Comment

by:tribz
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 13

Expert Comment

by:gbanik
Comment Utility
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
 
LVL 1

Author Comment

by:tribz
Comment Utility
Thanks, I'll have a go with this today!
0
 
LVL 1

Author Comment

by:tribz
Comment Utility
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
 
LVL 1

Author Comment

by:tribz
Comment Utility
Could I also ask why this hyperlinking problem comes about?
0
 
LVL 13

Expert Comment

by:gbanik
Comment Utility
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
 
LVL 1

Accepted Solution

by:
tribz earned 0 total points
Comment Utility
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
 
LVL 1

Author Closing Comment

by:tribz
Comment Utility
as per comment above
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now