Leading zero in hyperlink TextToDisplay

[Murray]

Hello

I have a macro that allows the user to insert a hyperlink to a pdf
file. The macro allows them to browse to a pdf file, click on it and
the link will be inserted. This seemed to be working fine until Excel
2003.

The pdf files are numbered, and may have leading zeroes (names are all
seven characters long).

Following are some fragments of my macro:

Sub CreateHyperlink()
Dim FName As String, DispName As String
'
Code to extract filename from full path and remove .pdf
'
'
' Create the hyperlink
'
ActiveSheet.Hyperlinks.Add Anchor:=ActiveCell, _
Address:=FName, TextToDisplay:=DispName
End Sub

If I "watch" the DispName variable, the code works correctly and it has
a seven digit value (such as 0654321). However, when inserted in the
spreadsheet the hyperlink drops the leading zero and displays 654321.
I've tried various formatting options. If I (via the macro) put an
apostrophe in front of DispName it shows up correctly (eg
TextToDisplay:="'" & DispName), but then the XL2003 error checking
kicks in and I get little green triangles everywhere.

Can anyone suggest what might have changed between 2000 and 2003, and
more importantly how I can prevent it from dropping the leading zeroes?

Thanks

Murray

 

[NickHK]

Murray,
What if you use the HYPERLINK WS formula instead:

ActiveCell.FormulaR1C1 = "=HYPERLINK(""C:\Hlink.xls"",""006542"")"

NickHK

 

[Murray]

NIckHK

Thanks for the suggestion. I tried that, and got it working. However,
this is in a server environment and using "HYPERLINK" seemed to use
drive letters rather than the full URL (eg W:\ rather than
\\servername). I didn't want to be dependent on a users drive mapping
which I can't necessarily control.

Perhaps I have gotten something wrong with my HYPERLINK code? It was
basically the same - I even inserted the same variables of Fname and
DispName after I had derived them.

Regards

Murray

 

[NickHK]

Murray,
I'm not curently connected to a server, so I can't test, but according to
Help:
<Excel Help>
"In Excel for Windows, the following example displays the contents of cell
D5 as the jump text in the cell and opens the file named 1stqtr.xls, which
is stored on the server named FINANCE in the Statements share. This example
uses a UNC path:
=HYPERLINK("\\FINANCE\Statements\1stqtr.xls", D5)
</Excel Help>

You mean this then gets resolved and altered to the mapped drive path
instead of the UNC path ?

NickHK

 

[Murray]

Brilliant!

As it turns out, my path variable contained the mapped drive path, and
the ActiveSheet.Hyperlinks command resolved it to a full UNC path,
rather than the other way around. I changed the code to pass the UNC
path to GetOpenFilename (courtesy of some code posted by Bob Phillips)
and now the =HYPERLINK() contains the UNC path and works fine.

Why the original code ceased to put the leading zeroes remains a
mystery. If anyone has an answer I'd be interested to know.

Thanks NickHK for your help

Regards

Murray