Open a pdf file based on partial text of a cell value in Excel VBA

[Cakz Primz]

Dear all,

I have cell value in cell K3 "TEP-TGH-MRR-CI-00460" based on this cell value, I would like to open a pdf file in certain folder only if the pdf file name start with K3 cell value.
The code below was found in internet, and it works ONLY if the pdf file name is exactly the same with K3 cell value.

How can I open the pdf file if the pdf file name started with cell K3 value?

Sub OpenPDF()
    ' Declare variables
    Dim filePath As String
    Dim cellValue As String

    ' Get the value of the cell
    cellValue = Range("K3").Value
    
    ' Specify the folder path
    filePath = "C:\"
    'filePath = "P:\7.LOG&MATERIAL\MATERIAL CONTROL\06-Warehousing\00 -Document Register\02 FINAL DOCUMENT\03-MRR\"

    ' Construct the full file path
    filePath = filePath & cellValue & ".pdf"

    ' Check if the file exists
    If Dir(filePath) = "" Then
        MsgBox "The file " & cellValue & ".pdf" & " does not exist in the folder."
    Else
        ' Open the file
        ActiveWorkbook.FollowHyperlink filePath
    End If
End Sub

Thank you very much in advance.
prima - Indonesia

 

[jolivanes]

Sub Open_PDF()
Dim pth As String, fName As String
pth = "C:\Test"    '<---- Change as required
fName = Dir(pth & "\*" & [K3] & "*.pdf")
ActiveWorkbook.FollowHyperlink pth & "\" & fName
End Sub

 

[Cakz Primz]

Sub Open_PDF()
Dim pth As String, fName As String
pth = "C:\Test"    '<---- Change as required
fName = Dir(pth & "\*" & [K3] & "*.pdf")
ActiveWorkbook.FollowHyperlink pth & "\" & fName
End Sub

Dear jolivanes,

Your code is working well.
Problem solved.
Thanks for everything.

Best regards,
prima - Indonesia

note:
How is the code to open a pdf file based on ActiveCell.Value (selected cell) ?

 

[Cakz Primz]

Dear jolivanes,

Your code is working well.
Problem solved.
Thanks for everything.

Best regards,
prima - Indonesia

note:
How is the code to open a pdf file based on ActiveCell.Value (selected cell) ?

Dear jolivanes,

Problem solved, based on your code, I modified as below. And it works.
Again, thank you very much for your kind assistance and help.

Sub Open_PDFx()
Dim pth As String, fName As String
curVal = ActiveCell.Value

pth = "C:\"    '<---- Change as required
fName = Dir(pth & "\*" & curVal & "*.pdf")
ActiveWorkbook.FollowHyperlink pth & "\" & fName
End Sub

Best regards,
Prima - Indonesia

 

[jolivanes]

Terima kasih

Are you sure this works?

pth = "C:\"    '<---- Change as required

Looks like the backslash is not needed as you have it later in the code.

 

[Cakz Primz]

Terima kasih

Are you sure this works?

pth = "C:\"    '<---- Change as required

Looks like the backslash is not needed as you have it later in the code.

Dear jolivanes,

Sure, it worked. I applied and ran the code, already.
The first time, I've tried based on C: as trial
But the actual drive is in share drive as below:

Sub Open_PDF2()

Dim pth As String, fName As String
curVal = ActiveCell.Value

pth = "P:\7.LOG&MATERIAL\MATERIAL CONTROL\06-Warehousing\00 -Document Register\02 FINAL DOCUMENT\03-MRR\"
'fName = Dir(pth & "\*" & [K3] & "*.pdf")
fName = Dir(pth & "\*" & curVal & "*.pdf")
ActiveWorkbook.FollowHyperlink pth & "\" & fName

End Sub

Thank you,
prima - Indonesia

 

[rcmistri]

Hi @jolivanes

I am quite new to vba and syntax is very difficult to understand (no offence !) compared to my experience in R and Python
below are my file details :

> I want to open file from the same folder where xlsm file is saved
> I have file names in Column A, and actual file names have additional characters and .pdf extension
such as xx-yy-nn-xxx-nnnnn will be cell value and related file name will be xx-yy-nn-xxx-nnnnn_0001_2-a.pdf
> the content of cell in column A becomes hyperlink if it matches the file available in the folder OR
a text like "Open File" becomes visible with hyperlink to file if the cell content matches with file available in the folder

I tried Cakz_Primz's code by inserting in the module but cant understand how it works and where the link will be.
also, is there any setting need to be changed to enable refreshing the xlsm file ? I have already enabled some settings to allow contents but still something dont seem working . . .
I have modified code and declared pth as below

Sub Open_PDFx()
Dim pth As String, fName As String
curVal = ActiveCell.Value

pth = Application.ActiveWorkbook.Path       '<---- Change as required
fName = Dir(pth & "\*" & curVal & "*.pdf")
ActiveWorkbook.FollowHyperlink pth & "\" & fName
End Sub