hyperlinking pdf to excel
Thread Starter
Join Date: Nov 1999
Location: Sunny Cyprus
Posts: 60
Likes: 0
Received 0 Likes
on
0 Posts
hyperlinking pdf to excel
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.
Join Date: Mar 2013
Location: Siliconia
Age: 63
Posts: 44
Likes: 0
Received 0 Likes
on
0 Posts
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
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
Spoon PPRuNerist & Mad Inistrator
but where do i type in
'dir /b > templist.txt' and do I type it in exactly as you've written?
'dir /b > templist.txt' and do I type it in exactly as you've written?
The point is that you are creating a text file from the directory listing of the folder containing the names.
Thread Starter
Join Date: Nov 1999
Location: Sunny Cyprus
Posts: 60
Likes: 0
Received 0 Likes
on
0 Posts
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
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
Gnome de PPRuNe
Join Date: Jan 2002
Location: Too close to Croydon for comfort
Age: 60
Posts: 12,662
Received 320 Likes
on
178 Posts
Ed Set, thank you, that copy as path tip is very useful indeed.
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
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