hyperlinking pdf to excel

[Ed Set]

I have an excel spreadsheet with around 200 people on it. I would like to be able to add a hyperlink to each individual that takes me to a particular document. I have a folder containing these pdf documents - one per person - and short of adding the hyperlink individually (which would take ages) I can't find a way of linking them. Hope you can help.

[noughtsnones]

I've done similar in a few steps...
1. Create a list of files to link to ... e.g. in the folder of pdf files, type 'dir /b > templist.txt'
2. Open templist.txt in an editor (e.g. Notepad, Notepad++, Wordpad or ...), "Select All" and "Copy"
3. "Paste" into a new column (say A) in EXCEL, probably best to trial in a fresh sheet
4. In new column (say B), fill down the formula "=concatenate("file:///C:/Users/Cyprus/Ed-Set/",A1,".pdf ")
5. Copy column B to required location and Paste Spacial Values i.e. not formulas.

Obviously C:/Users/Cyprus/Ed-Set/ should be replaced by the actual user directory structure.

I've used file:/// above (with three / characters) because I've copied from some html (browser page edit) notes; for EXCEL local use only, you may need fewer / characters.
0'n'1

[Ed Set]

Many thanks - I'll give it a try. Silly question perhaps but where do i type in
'dir /b > templist.txt' and do I type it in exactly as you've written?

[Saab Dastard]

but where do i type in
'dir /b > templist.txt' and do I type it in exactly as you've written?

In a command prompt, and yes, apart from the ' at each end.

The point is that you are creating a text file from the directory listing of the folder containing the names.

[Ed Set]

Many thanks for your help noughtsnones and Saab Dastard- it worked well and did what it was supposed to!!
I did however find an alternative way of adding the file addresses in a folder to my spreadsheet.
Firstly, open the folder and then select the files for which you want the file names list. Hold down the SHIFT key and RIGHT click on the selected files. You will see an option called "Copy as Path". Now go to Word/Notepad/Excel and click PASTE. You will get a list of the file names, including the directory path for each file.
Then it was just a case of creating the hyperlinks in Excel.
Your help was greatly appreciated - thanks again.
Ed

[treadigraph]

Ed Set, thank you, that copy as path tip is very useful indeed.

[Saab Dastard]

Ed Set, that's something I've never come across before, thanks for pointing it out.

SD

[India Four Two]

Ed Set,

I didn't know that either.

For Mac users, I have discovered that you can copy the path from the Get Info window:
Quickly Copy a File or Folder Path to the Clipboard in Mac OS X | OSXDaily