Solved

Excel 2003 Hyperlinking path problem

Posted on 2010-11-10
15
511 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
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Filter an Excel list by multiple criteria 6 38
Excel vlookup and or INDEX and MATCH 16 45
Help to break down spreadsheet 3 38
Access 2010 7 33
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

735 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