Excel 2007 : Hyperlink with hidden path & only shows filename?

Hi

I looked at using VBA and the following should do what you require.

Sub add_hyper()

For crow = 2 To 74

With Worksheets(1)

Let a = Format(Cells(crow, 2), "hh:mm")
Let b = Format(Cells(crow, 1), "mm-dd-yy")
Let C = Format(Cells(crow, 2), "hh mm")

    .Hyperlinks.Add Anchor:=.Range("c" & crow), _
        Address:="C:\Root\4x\trades\2009 forward\01 USD-CAD\" & b & " " & C & ".pdf", _
        ScreenTip:=b & " " & C & ".pdf", _
        TextToDisplay:=a & ".pdf"
End With

Next crow

End Sub

To use the code you will need to delete existing entries then run the macro, the code should be in a standard module. One point to note is that in order to prevent users seeing/finding the hyperlink the sheet must be protected, also the screentip MUST contain some text as otherwise it will display the path.

If you have any questions please let me know.

regards

Jeff

Related thread: http://www.excelforum.com/excel-2007...d-columns.html

Originally Posted by DonkeyOte

Related thread: http://www.excelforum.com/excel-2007...d-columns.html

DonkeyOte and Solnajeff,
Thanks a lot for all of your help. I finally arrived at a formula that works beutifully:

=HYPERLINK("C:\Root\4x\trades\2009 forward\01 USD-CAD\"&TEXT($A14,"mm-dd-yy")&" "&TEXT($B14,"hh mm")&".pdf",TEXT($A14,"mm-dd-yy")&" "&TEXT($B14,"hh mm")&".pdf")

This formula hides the entire path and only shows the filename. Very Cool! Thanks again to everyone for their help and I'm closing this thread as SOLVED.