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
Rename the workbook to Index Delete the Path and Item Type Columns
Select Column A -> Right Click -> Remove Hyperlinks
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.