Generate links to external files in Excel

November 16, 2007 3:23 AM

In MS Excel, is there any way to take a cell value and use it to search A folder for a file with the same name, then add a link to it?

I have an Excel worksheet (Excel 2000) that contains about 1600 rows of information about tickets. I also have a folder that contains about 800 or so ticket scans (in pdf format) all named by ticket number.

I would like to be able to take a cell value containing the ticket number, use it to search in a folder for the corresponding pdf and if a pdf exists, add a link to it in the worksheet.

Is this possible without delving into VBA? I am a bit of a VBA novice...

posted by davehat to Computers & Internet (7 answers total)

Yeah, that's something you would do with VBA. I think you use the FileSystemObject and some other related classes to look for files.
posted by douglaswth at 4:56 AM on November 16, 2007

Perhaps you could hire someone on rentacoder.com to do it for you. It might even be cheap.

It will be really easy for a professional. In fact, I would be an interesting exercise for a VBA novice like you.
posted by gmarceau at 6:04 AM on November 16, 2007

Is this something you would like to be updated automatically, or do you need it done for one time only? In other words, will new files be added to the folder, such that when they are added do you need a new corresponding link to appear automatically in the Excel file?

If not, there might be a simple solution:

1) ASAP Utilities is a free Excel add-in that allows you to automate many frequently used tasks in Excel (examples: automatically converting cells to all uppercase, lowercase, or sentence case; trimming spaces or removing any other character you choose from the cells, remove all hyperlinks, merge multiple files into a single file, highlight every other row, etc.). Download it from http://www.asap-utilities.com and install on your computer.

2) Under the new "ASAP Utilities" menu, choose "information" --> "list filenames in folder". Select the folder that includes your PDF files. This will generate a new worksheet that includes a listing of each file, with the name hyperlinked to the actual file.

3) Now with a little manipulation and using VLOOKUP or MATCH, you can simply create a function that will look for the cell value in the worksheet that ASAP created, and if the value exists, show the filename with the hyperlink on the current worksheet.


If this sounds like what you want to do, but can't figure out how to get Step 3 to work, let me know a little more (what do the cell values look like, and what do the filenames look like) and I might be able to help you write a simple function to accompllish that..


Again, the main caveat is that, ASAP only takes a snapshot of the folder. So, if you add new files into the folder, the worksheet will not be automatically updated. You would have to re-run the above steps every time the contents of the folder changes...

I think if the folder changes frequently, its better to delve into VBA to handle this dynamically...
posted by tuxster at 8:32 AM on November 16, 2007

1. Create a FileExists Function....

Tools --> Macro --> Visual Basic Editor
Rich Click "Microsoft Excel Objects"
Click "Insert Module"
Add the following code to the VBA ...

Click back into "View Microsoft Excel" (Top right Excel Button)

Private Function FileExists(fname) As Boolean
' Returns TRUE if the file exists
Dim x As String
x = Dir(fname)
If x <> "" Then FileExists = True Else FileExists = False
End Function

2. Add the following function to the new column ... (Where A2 contains the full filename you want to check)

=IF(fileexists(A2),(HYPERLINK(A2)),"")

To get the full filename into A2, take The FileName and Concatenate it to the Folder the files exist in...

That might do what you want.
posted by seanyboy at 9:05 AM on November 16, 2007

The "Click back into "View Microsoft Excel" (Top right Excel Button)" line should be below the code you need to paste into the Module VBA screen.

The VBA starts with "Private Function ..." and ends with "End Function"
posted by seanyboy at 9:06 AM on November 16, 2007

Damn - I was bored, so I made an example excel spreadsheet.

Hopefully it make sense.
posted by seanyboy at 9:18 AM on November 16, 2007

I've spent some time messing about with this and even though I eventually bodged a solution using FileSystemObject and a bit of matching, seanyboy's solution seems to be a bit easier given a need to explain to other people what the sheet is doing.

Its also clear that I need to go and get some Excel and Access VBA training if I'm going to be able to do what I'm being asked to do at work.

Thanks for your help guys and many thanks for going the extra mile seanyboy - the sample sheet clearly demonstrates how your (rather elegant) solution works.
posted by davehat at 3:06 PM on November 17, 2007