Macro to open pdf file based on a cell value.

[martin61]

How can I code a Macro in Excel to search in a preset directory and open a certain Adobe Acrobat .pdf file ?

Example: If cell L1 has a date of 5-Jan, I want it to open a pdf file called 5-Jan.pdf, which i can do with the following:

Sub Payslip()
MyPath = "C:\Program Files (x86)\Adobe\Reader 9.0\Reader\AcroRd32.exe"
MyFile = "C:\Users\Dads\Documents\Payslips\2012\5 Jan.pdf"
Shell MyPath & " " & MyFile, vbNormalFocus
End Sub

But when the date in cell L1 changes to say 12-Jan the above is no good.
I want it to open pdf file based on the value of cell L1.
Any help appreciated Thanks.

 

[GopherUK]

If you want it to automatically trigger on change, you will need to build a selection change macro in the worksheet object.

I suggest using a switch case statement to choose which PDF to open, based on the value it finds in the target cell.

HTH.

 

[martin61]

Hi GopherUK, thank you for ur reply.
I dont want it to automatically trigger on change, I want to click on a button to activate macro.
So if cell L1 has a value of 5-Jan when i click on button it opens pdf file named 5-Jan.pdf, or if L1 has value of 12-Jan click on button to open pdf file named 12-Jan.pdf

Thanks Again..

 

[GopherUK]

Sub Payslip()

    MyPath = "C:\Program Files (x86)\Adobe\Reader 9.0\Reader\AcroRd32.exe"
    MyFile = "C:\Users\Dads\Documents\Payslips\2012\" & Range("L1") & ".pdf"
    Shell MyPath & " " & MyFile, vbNormalFocus

End Sub

That should do it. Insert a shape or something into the sheet then attach that macro to it.

 

[martin61]

GopherUK thanks again for ur reply.
It trying to work, it opens acrobat but i get the following error message:
"there was a error opening this document" this file cannot be found.
Cell L1 shows value of "5 Jan" formated as Custom "d mmm"
Pdf file is named 5 Jan.pdf
Im not sure why it cant find it.

 

[GopherUK]

L1 is a date cell?

 

[martin61]

Yes

 

[GopherUK]

This should work.

Sub Payslip()

    Dim FileNameRef As String
    
    FileNameRef = Application.WorksheetFunction.Text(Range("L1"), "d mmm")

    MyPath = "C:\Program Files (x86)\Adobe\Reader 9.0\Reader\AcroRd32.exe"
    MyFile = "C:\Users\Dads\Documents\Payslips\2012\" & FileNameRef & ".pdf"
    Shell MyPath & " " & MyFile, vbNormalFocus

End Sub

 

[martin61]

Works perfectly !!
Thank you for your help, much appreciated.

 

[GopherUK]

No probs