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.

Chrissy has worked in IT for nearly 20 years, and currently serves as a SQL Server DBA for a federal contractor 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 RealCajunRecipes.com in her spare time. She holds a number of certifications, including those relating to SQL Server, SuSE Linux, SharePoint and network security. 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 *

*

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="">

About

We are IT pros who grew up living and loving life in Cajun Country.

Authors


Chrissy LeMaire
View Chrissy LeMaire, BSc. MCITP's profile on LinkedIn


Brandon Abshire
View Brandon Abshire, MCDBA's profile on LinkedIn