ipbr21054
Well-known Member
- Joined
- Nov 16, 2010
- Messages
- 5,596
- Office Version
- 2007
- Platform
- Windows
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
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
VBA Code:
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