SharePoint/Excel 2010: Relative Hyperlinks and Local Documents

Relatively linking to local documents is much simpler you'd expect. Just save the Excel file and the documents in the same directory and =HYPERLINK(filename,filename). No need for "file://" or any fancy hyperlinking. This method works anywhere -- your local drive, a CD-ROM drive, USB stick, or a networked drive.

If you'd like to add files to a subfolder, the syntax is =HYPERLINK("foldername\" & Name,Name) I've seen a few people around the 'net who also needed to download documents in SharePoint and have them indexed in Excel. Here's how you do it:


Go to the Document Library Download the documents to your local drive using Explorer View. Once that's done, select the View and Folder you'd like to export Ribbon Library Tools -> Library -> Current View, Change to CDR Don't click any files or folders -> Export to Excel Open


Click "Enable" if given a security prompt Now you're in Excel Click Data -> Connections -> Highlight owssvr -> Remove

Screen Shot 2012-04-15 at 6.48.45 PM

Rename the workbook to Index Delete the Path and Item Type Columns

Screen Shot 2012-04-15 at 6.46.44 PM

Select Column A -> Right Click -> Remove Hyperlinks

Screen Shot 2012-04-15 at 6.49.25 PM
Select Column A2 -> Right Click -> Insert Table Columns to the Left

Screen Shot 2012-04-15 at 6.54.36 PM

Name that column FileName Click the "Name" Column to highlight the whole thing Click "Formulas" in the Ribbon -> Define Name -> It should say Name, Scope is Workbook, Refers to is autofilled: =Index!$B:$B

Screen Shot 2012-04-15 at 9.14.37 PM

Highlight all of the empty cells on the side of the Filenames (A2 on down) Go to the Function section Paste: =HYPERLINK(Name,Name)


Save to all columns by clicking CTRL-ENTER


Save the Excel file to the same directory where you initially copied your SharePoint directories.

*BONUS: Highlight the Name column and hide it.