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

**binar** · 02-13-2009, 10:44 PM

Fellow Forum Members,
What needs to be changed to this formula:

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

So that the path shown below is hidden in all of my hyperlinks:

C:\Root\4x\trades\2009 forward\01 USD-CAD

I want the hyperlink to only show the PDF filename. As shown below:

02-12-09 10 00.PDF

The attached Excel file will give you a better idea.

In the past I have seen hyperlinks where the PATH is hiding. How is this done? ANy help will be greatly appreciated. THanks.

**solnajeff** · 02-13-2009, 11:17 PM

Hi

Normally when you create a hyperlink by selecting a file from the dialog box you have the option to choose both the text you wish shown in the cell and also any tool tip text as well.

Possibly you could make this work by pasting in the link text you have at this point, although I think possibly not. Plus the fact is it would be very time consuming.

I would think the best option is to use VBA, but I would need to check out the possibilities.

Regards

Jeff

**binar** · 02-13-2009, 11:46 PM

Solnajeff,

Thanks for the post. Attached you will find an Excel Addin that does something very cool. It automatically creates a list for you by drilling down into any path you select and then creates a hyperlinked list of any file extension you specify.

The hyperlinks this Addin creates is hiding the path in all the hyperlinks. It only shows a hyperlinked filename.

Can anyone out there look into this XLA file and let me know how this addin is hiding the entire path in a hyperlink and just showing a hyperlinked filename? I prefer only a hyperlinked filename because it's looks much more cleaner.

Any help will be greatly appreciated.

Thanks.

Originally Posted by solnajeff

Hi

Normally when you create a hyperlink by selecting a file from the dialog box you have the option to choose both the text you wish shown in the cell and also any tool tip text as well.

Possibly you could make this work by pasting in the link text you have at this point, although I think possibly not. Plus the fact is it would be very time consuming.

I would think the best option is to use VBA, but I would need to check out the possibilities.

Regards

Jeff

**solnajeff** · 02-14-2009, 12:00 AM

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

**DonkeyOte** · 02-14-2009, 04:08 AM

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

**binar** · 02-14-2009, 01:18 PM

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.