How to Suppress UNC Replacement in Hyperlink Objects

[Oralloy]

Folks,

We have an interesting problem here in Excel 2007. I don't know if this problem appears in any other versions, or not.

Essentially I'm using VBA code to update hyperlinks, replacing the \\system-name\share-name with an abstract drive letter. Unfortunately, microsoft is translating the URL and converting the drive letter back into the \\system-name\share-name form.

Do any of you know how I can suppress this automatic replacement?

Here is an abstracted version of the code:

Sub Hyperlinks_Find_Replace()
''
''  Macro to find/replace update the hyperlink targets in
''  the active workbook.
''
  ''--string to replace
  Dim sOld As Variant
  sOld = "\\server\ShareName"
 
  ''--replacement value
  Dim sNew As Variant
  sNew = "P:"
 
  ''--which workbook?
  Dim book As Excel.Workbook
  Set book = Excel.ActiveWorkbook
 
  ''--work off each sheet
  Dim sheet As Excel.Worksheet
  For Each sheet In book.Worksheets
 
    Dim hLink As Excel.Hyperlink
    Dim vOld As String
    Dim vNew As String
 
    For Each hLink In sheet.Hyperlinks
      vOld = hLink.Address
      vNew = VBA.Replace(vOld, sOld, sNew)
 
      If vOld <> vNew Then
        hLink.Address = vNew
      End If
    Next hLink
 
  Next sheet
 
End Sub
 

And yes, I have verified in the debugger that the update is being done, and that it is ineffectual.

I can use the same loop and replace the share name with no difficulty.

Thank You,
Oralloy

[NeoPa]

Before I go any further, can I assume when you refer to URLs in your question you're really meaning UNCs?

Regardless of the answer, you have my full attention. You answered a number of my threads in SQL a while back so you have plenty credit :-)

While you're posting, can you explain where the offending automatic change is happening (A line # would be good).

[NeoPa]

I know you've been having difficulties posting recently so I'll update that we chatted together and tested and came to the conclusion that, as far as we were able to tell at least, this behaviour on the part of Excel with regard to hyperlinks to network drives is simply how it works. It's not optional. It just changes it automatically back to the UNC notation whenever it finds any reference in the .Address to a networked drive.

An option may be to set up a machine where the P: drive is not networked but reflects the same structure as your networked drive (at least as far as all the hyperlinked items go - You can use the DOS command SUBST to do this.), run the code (to change all the addresses to the P: format) within this setup then save it. This version should work when opened anywhere else.