We help IT Professionals succeed at work.

Links within Excel files having two dots instead of full path

alexisbr
alexisbr asked
on
Hi.  We are trying to change the path in hyperlinks in Excel after moving servers.  We now realize that the paths all have ".." in front rather than a hard-coded path.  We cannot fix these paths using a VB program.  Why does Excel do this instead of using a mapped drive or UNC path?  I put a screenshot below showing how the hyperlink looks.  We are running Excel 2010, however, we believe it was the same thing in Excel 2003 but we never had to change the paths programmatically before.

Is there a setting in Excel to tell it to use mapped drives or the UNC path and not ".."?

Thanks,
Alexis
preceding-dots-Excel-path.png
Comment
Watch Question

Top Expert 2011

Commented:
Are you sure it's a problem - the .. notation just means 'back one level of directory from the current location' as I'm sure you know.  If you have moved servers, won't this still be correct?
Top Expert 2011
Commented:
If you do need to fix, this works for me:
Public Sub FixHyper()

    Dim h As Hyperlink
    Dim strAddress As String
    
    Dim strCurrPath As String
    Dim strCurrPathUp As String
    Dim x As Long
    
    strCurrPath = ActiveWorkbook.Path
    x = InStrRev(strCurrPath, "\")
    If x > 0 Then
        strCurrPathUp = Left$(strCurrPath, x - 1)
    End If
    
    For Each h In ActiveSheet.Hyperlinks
        strAddress = h.Address
        
        If Left$(strAddress, 2) = ".." Then
            strAddress = strCurrPathUp & Mid$(strAddress, 3)
            h.Address = strAddress
        End If
        
    Next h

End Sub

Open in new window

BRONZE EXPERT
Commented:
This snippet is from a MS support page and it helps when setting new links up  - but you will need code like the above to manage existing sheets.

http://office.microsoft.com/en-us/excel-help/create-select-edit-or-delete-a-hyperlink-HP010342381.aspx?CTT=1
>>
By default, unspecified paths to hyperlink (hyperlink: A word, phrase, picture, icon, symbol or other element in a computer document or webpage on which a user may click to move to another part of the document or webpage or to open another document, webpage, or file.) destination files are relative to the location of the active workbook. Use the following procedure when you want to set a different default path. Each time that you create a hyperlink to a file in that location, you have to specify only the file name, not the path, in the Insert Hyperlink dialog box.

Click the File tab.
On the Information about <workbook name> page, in the far right pane, click Document Properties, and then click Advanced Properties
Click the Summary tab.
In the Hyperlink base box, type the path that you want to use.
Note    You can override the hyperlink base address by using the full, or absolute, address for the hyperlink in the Insert Hyperlink dialog box.

Click any other tab to return to your file.

Author

Commented:
andrewssd3:
"Are you sure it's a problem - the .. notation just means 'back one level of directory from the current location' as I'm sure you know.  If you have moved servers, won't this still be correct? "

Normally, yes, this wouldn't be a problem but we didn't just move the folders to the same directory structure.  We restructured our folder setup so the ".." is a big problem.  

Thanks for the code.  We will be testing it out soon.

Regmigrant:  Thanks for the link and info.  

Alexis

Author

Commented:
Thanks for your help.  You helped me think this through.  After reviewing the hyperlinks that needed to be changed, I realized that they all did not begin with ".." so I just checked for that and then added the correct prefix path.  I used the code below to make the actual changes.  We are still testing but if I need more help, I will do another post.

Thanks again.
Alexis
 
Sub FixHyperlinks()
Dim hyp As Hyperlink, i As Integer
i = 0
For Each hyp In ActiveSheet.Hyperlinks
  i = i + 1
  'If i > 155 And i < 170 Then
   If Left(hyp.Address, 2) <> ".." Then
   ' MsgBox i & " " & hyp.Address
    hyp.Address = "..\..\OLEDs2008\" & hyp.Address
   End If
  'hyp.Address = Replace(expression:=hyp.Address, Find:=OldStr, Replace:=NewStr, compare:=vbTextCompare)
Next hyp
MsgBox "done"
End Sub

Open in new window