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


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.

Chrissy is a PowerShell MVP who has worked in IT for nearly 20 years, and currently serves as a Sr. Database Engineer in Belgium. Always an avid scripter, she attended the Monad session at Microsoft’s Professional Developers Conference in Los Angeles back in 2005 and has worked and played with PowerShell ever since. Chrissy is currently pursuing an MS in Systems Engineering at Regis University and helps maintain in her spare time. She holds a number of certifications, including those relating to SQL Server, SuSE Linux, SharePoint and network security. She recently became co-lead of the SQL PASS PowerShell Virtual Chapter. You can follow her on Twitter at @cl.

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 *