asked on

Links within Excel files having two dots instead of full path

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

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?

ASKER CERTIFIED SOLUTION

andrewssd3

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

SOLUTION

regmigrant

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

alexisbr

ASKER

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

alexisbr

ASKER

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