VBA, Hyperlink to PDFs Question

[Blueridge227]

My situation is as follows:

I have a spreadsheet with column A consisting of a bunch of 4 digit numbers(1028, 1056 etc..) The 4 digit numbers are the same as the filenames of several PDFs in a folder on my local drive(C:\Test\).

I'm looking for a VBA that will search through the PDFs in the folder (based on the 4 digit numbers from column A) and place a Hyperlink to the file in Column B

If no match is found, then I need the cell in column B to remain blank(no hyperlink)

Any help would be greatly appreciated!
cheers

 

[Blueridge227]

I should also mention I'm using Excel 2007

 

[Nemon]

I joined to ask the very same question. I did a search but didn't find any similar issues.

 

[Nemon]

My situation is as follows:

I have a spreadsheet with column A consisting of a bunch of 4 digit numbers(1028, 1056 etc..) The 4 digit numbers are the same as the filenames of several PDFs in a folder on my local drive(C:\Test\).

I'm looking for a VBA that will search through the PDFs in the folder (based on the 4 digit numbers from column A) and place a Hyperlink to the file in Column B

If no match is found, then I need the cell in column B to remain blank(no hyperlink)

Any help would be greatly appreciated!
cheers

Hi, I "borrowed" your thread as you can see, been looking for an answer to this. I found this solution today however, maybe it will work for you? I'm on excel 2010 btw...

Enter this formula in B1:
=IF(A1=0;" ";HYPERLINK("C:\Test\"&A1&".pdf";"Link"))

It should leave the B1 blank when A1 is blank, and a "Link" when A1 has a value entered like one of your pdf's.

Sorry for double posting, couldn't see an edit option. But hopefully this is a solution to your problem.

 

[Blueridge227]

Thanks for your reply. Unfortunately I can't use your suggestion. There will always be a value(filename) in the cell. What I was hoping for is when I place the pdf in the folder, excel would automatically place a hyperlink to the file with the same filename.


This is what i'm using in the meantime.
=HYPERLINK("X:\PT RECORDS\"&A:A&"\"&E:E&".pdf",E:E)

Column A has the folder name
Column E is the filename.

This creates a hyperlink to everything and gives a "Cannot Open File" messege if the file doesn't exist.