A Button to open specified file?

[bnapy]

I'm a longtime Lurker first time poster, I've gotten alot of information from this site and want to thank all the contributors. I need a solution to a small problem I have.

I have a reconciliation worksheet that I've created, and I'm in the process of scanning the corresponding Invoices to my computer. I'm wondering if it's possible to make a button on the worksheet that would open the corresponding invoice (PDF FILE) given a specific value of a Cell.

For instance all the scans are labeled in numeric order. These labels will be on the corresponding expense row on the spreadsheet. I'd like a Cell that says "Enter Label here" which will reference the scanned invoice and a button you push that will launch the referenced PDF file.

Any help would be much apprieciated...

 

[AlphaFrog]

You could do it with just a formula.

If you enter the Invoice number in say cell B2, put this in cell C2

=IF(B2="","",HYPERLINK("C:\Temp\" &B2&".pdf"))

This will create a clickable hyperlink in cell C2. Change the file path in the formula to suit.

 

[bnapy]

Thats an awesome ideal, I can't believe i didn't think of that. No wonder i couldn't google an answer to something so simple...Thank you.

Now I'm wondering how to make the hyperlink so that it will always look for the requested file in the same directory as the spreadsheet is located. The spreadsheet will be copied to multiple flash drives and depending on the system. The drive may be labeled D:, F:, etc...I'm wondering if excel corrects for this?

 

[AlphaFrog]

You could have the user type in a drive letter in say cell A2

=IF(A2="","Missing drive letter", IF(B2="", "Enter Invoice number", HYPERLINK(A2&":\Temp\"&B2&".pdf")))

Better yet, have a Data Validation drop-down list for the drive letters.

 

[bnapy]

Thanks for the quick response. I'm looking into the Data Validation option now. If that doesn't pan out I'm going to use your "two cell" suggestion.

Again Thank You! It is much apprieciated!

 

[AlphaFrog]

This formula will return the path of the excel file...

=MID(CELL("filename"),1,FIND("[",CELL("filename"))-1)

CELL function

Insert the current Excel file name, path, or worksheet in a cell

 

[bnapy]

Everything worked perfectly, again thank you for your help. I've been google'n my last problem and think I have found a solution regarding the ability to open a pdf to a specific page.

I found some code that creates a function called GoToPDF so that was the last bit I had to get help on.

Alpha I really apprieciate your help!