Hyperlink file in pdf format

[ipbr21054]

Hi,
I am using the code shown below to hyperlink some pdf files BUT now need an alteration.
The below code works fine if its just a customers name.

Now i have some files (250+) which are saved in the same folder like so,

TOM JONES 01-06-23 366A76
FRANK SINATRA 05-09-22 2DBD1C

So if i use the code shown below then i will see the Msg There is no file for this customer when looking for the above.
Reason being the code below is just looking for the customers name.

All the files consist of the customers name, the date & a 6 string code followed by .pdf

Is there a workaround so the code just looks for say TOM JONES when the file is actually saved as TOM JONES 01-06-23 366A75

Private Sub HyperlinkDisco_Click()
 Const FILE_PATH As String = "C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\DISCO II PDF\"
        If ActiveCell.Column = Columns("B").Column Then
          
        If Len(Dir(FILE_PATH & ActiveCell.Value & ".pdf")) Then
        ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:=FILE_PATH & ActiveCell.Value & ".pdf"
        MsgBox "HYPERLINK WAS SUCCESSFUL.", vbInformation, "POSTAGE SHEET HYPERLINK MESSAGE"
        End If
        
        Else
        MsgBox "PLEASE SELECT A CUSTOMER FIRST TO HYPERLINK THE FILE.", vbCritical, "POSTAGE SHEET DISCO II HYPERLINK MESSAGE"
        Exit Sub
        End If
        
        If Dir(FILE_PATH & ActiveCell.Value & ".pdf") = "" Then
        If MsgBox("THERE IS NO FILE FOR THIS CUSTOMER" & vbNewLine & "WOULD YOU LIKE TO OPEN THE DISCO II FOLDER ?", vbYesNo + vbCritical, "HYPERLINK CUSTOMER DISCO II MESSAGE.") = vbYes Then
        CreateObject("Shell.Application").Open ("C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\DISCO II PDF\")
        End If

    
    End If
End Sub

 

[John_w]

Is there a workaround so the code just looks for say TOM JONES when the file is actually saved as TOM JONES 01-06-23 366A75

Use the '*' wildcard character in the Dir string so it looks for "TOM JONES*.pdf".

Replace:

        If Len(Dir(FILE_PATH & ActiveCell.Value & ".pdf")) Then
        ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:=FILE_PATH & ActiveCell.Value & ".pdf"

with:

Dim file As String
file = Dir(FILE_PATH & ActiveCell.Value & "*.pdf")
If Len(file) Then
    ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:=FILE_PATH & file

and:

        If Dir(FILE_PATH & ActiveCell.Value & ".pdf") = "" Then

with:

        If Dir(FILE_PATH & ActiveCell.Value & "*.pdf") = "" Then

 

[ipbr21054]

Thanks for the reply BUT im an idiot & forgot to mention something.

Each customers name on the sheet im using to hyperlink has a ref code after it.

So still like the original post TOM JONES is actually TOM JONES 001

Returing customers are like so 002 or 003 or 004

Can you advise please.
Sorry for not mentioning in first post

 

[John_w]

In that case the two changes to my post would be:

Dim customerName As String
customerName = Left(ActiveCell.Value, InstrRev(ActiveCell.Value, " ") - 1)
file = Dir(FILE_PATH & customerName & "*.pdf")
If Len(file) Then
    ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:=FILE_PATH & file

and

        If Dir(FILE_PATH & customerName & "*.pdf") = "" Then

 

[ipbr21054]

Thanks,
Did i miss something as getting Variable not defined on this line.

file = Dir(FILE_PATH & customerName & "*.pdf")

This is the code i have in use at present.

Private Sub HyperlinkDisco_Click()
        Const FILE_PATH As String = "C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\DISCO II PDF\"
        If ActiveCell.Column = Columns("B").Column Then
          
        Dim customerName As String
           customerName = Left(ActiveCell.Value, InStrRev(ActiveCell.Value, " ") - 1)
           file = Dir(FILE_PATH & customerName & "*.pdf")
        If Len(file) Then
           ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:=FILE_PATH & file
           MsgBox "HYPERLINK WAS SUCCESSFUL.", vbInformation, "POSTAGE SHEET HYPERLINK MESSAGE"
        End If
        
        Else
           MsgBox "PLEASE SELECT A CUSTOMER FIRST TO HYPERLINK THE FILE.", vbCritical, "POSTAGE SHEET DISCO II HYPERLINK MESSAGE"
        Exit Sub
        End If
        
        If Dir(FILE_PATH & customerName & "*.pdf") = "" Then
        If MsgBox("THERE IS NO FILE FOR THIS CUSTOMER" & vbNewLine & "WOULD YOU LIKE TO OPEN THE DISCO II FOLDER ?", vbYesNo + vbCritical, "HYPERLINK CUSTOMER DISCO II MESSAGE.") = vbYes Then
           CreateObject("Shell.Application").Open ("C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\DISCO II PDF\")
        End If

    
    End If
End Sub

 

[ipbr21054]

UPDATE
I removed Option Explicit at the top & it works

 

[John_w]

I removed Option Explicit at the top & it works

I don't recommend doing that. You just need the Dim file As String.