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.

Posted in SharePoint
One comment on “SharePoint/Excel 2010: Relative Hyperlinks and Local Documents
  1. Brandon says:

    I couldn't get it to work It never explains how you got the original filename inside of the file name column. Key Details are missing great article extremly useful I really need this to work for me!

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">