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:

SharePoint

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

Excel

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

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

Select Column A -> Right Click -> Remove Hyperlinks

Select Column A2 -> Right Click -> Insert Table Columns to the Left

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

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.