Solved

Excel 2003 Hyperlinking path problem

Posted on 2010-11-10
15
515 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
15 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
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

688 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