How to create a hyperlink to open a file with part of the name

[tsraj]

I am trying to create a Hyperlink to open a file from an Excel spreadsheet.
Only a part of the file name (a few characters) is in the spreadsheet. I
tried using * as wild card character to represent other parts of the name.
ex: =HYPERLINK("e:\Disc 1\MP 200\","*"&K1018&"*"&".PDF"). Cell K1018
contains part of the file name. When I click on the Hyperlink it takes me to
the subfolder but it is not opening the image file. Do I have to use any
other character for the wild card?

Any help in this regard is very much appreciated.

 

[Tom Hutchins]

I don't think you can use wildcards in the filename with HYPERLINK. In any
case, you need to give the path and filename in the first argument. The
second argument is just the text to be displayed in the cell containing the
HYPERLINK function. For example,

=HYPERLINK("e:\Disc 1\MP 200\*" & K1018 & "*.PDF","Click here")

But this doesn't work because of the wildcard characters. I tried other
common wildcard characters but they didn't work either. If it can be done,
maybe an MVP knows how and will teach us both.

Hope this helps,

Hutch

 

[tsraj]

Thank you very much, Tom. I hope an MVP answers this.
FYI.. I also tried this formula =HYPERLINK(CONCATENATE("e:\Disc 1\MP
200\","*K1018*"&".PDF"),K1018).
It didn't work either. Thanks again.

 

[Dave Peterson]

I don't think you're going to get a hyperlink to find that file.

Maybe you could use the actual filename--or replace the hyperlink with a macro
that will look for a file that matches that specification.

I'd add a button from the Forms toolbar and place it in row 1 of the worksheet.
Freeze row 1 so that it's always visible and assign this macro to that button.

Option Explicit
Sub testme()

Dim TestStr As String
Dim JustPath As String
Dim myCell As Range

Set myCell = ActiveCell

TestStr = ""
On Error Resume Next
TestStr = Dir(myCell.Value)
On Error GoTo 0

If TestStr = "" Then
MsgBox "File not found"
Else
JustPath = Left(myCell.Value, InStrRev(myCell.Value, "\"))
ThisWorkbook.FollowHyperlink Address:="file:////" & JustPath & TestStr
End If

End Sub

Tell the user (in an adjacent cell???) to select the cell with the file to open
and click the button (the macro uses the activecell).

And you wouldn't use the =hyperlink() function with this.

It would be a simple concatenation:
="E:\disc 1\mp 200\*"&k1018&"*.pdf"

This macro doesn't do much checking, so you could break it pretty easily.

 

[tsraj]

Dave,

Thank you very much. It worked. Have a nice day.