?
Solved

Excel 2003 Hyperlinking path problem

Posted on 2010-11-10
15
Medium Priority
?
536 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
14 Comments
 
LVL 13

Expert Comment

by:gbanik
ID: 34100684
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
ID: 34100691
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
ID: 34100829
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 13

Expert Comment

by:gbanik
ID: 34101145
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
ID: 34101168
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
ID: 34111705
Did your query get resolved?
0
 
LVL 1

Author Comment

by:tribz
ID: 34112433
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
 
LVL 13

Expert Comment

by:gbanik
ID: 34114999
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
ID: 34134823
Thanks, I'll have a go with this today!
0
 
LVL 1

Author Comment

by:tribz
ID: 34134841
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
ID: 34134864
Could I also ask why this hyperlinking problem comes about?
0
 
LVL 13

Expert Comment

by:gbanik
ID: 34137073
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
ID: 34384481
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
ID: 34415225
as per comment above
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
Currently, there is an issue with being able to copy values from an external application to a dropdown list in Project Web Access (PWA).  The standard copy and paste methods don't seem to work properly. Here is a way to accomplish this task to s…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

850 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