Monday, November 30, 2015

How to Create an Export to Excel Link on Any List or Library for 2010 and 2013

A common complaint about SharePoint is the fact that in order to export a list or library to Excel, the user has to navigate to each list and select the option from the ribbon! Most companies I have worked with have a need for a centralized location that they can use as a hub to obtain their key metrics.   Now I don’t know if it is just me, but as the SharePoint guy I wouldn’t want to tell my executives that in order to do this that they will have to navigate to each data source and select Export to Excel from the ribbon.  Well if you do have this issue you are in luck! This article will walk through how to construct an Export to Excel URL which could be applied to an HTML button, the quick launch, the navigation, an image, a links list… well you get the gist.

What does this magical URL look like you may be wondering, well essentially you will just need to swap out the placeholders below with your values:

Template URL:
SITEURL/_vti_bin/owssvr.dll?CS=109&Using=_layouts/query.iqy&List={YOUR LIST ID}&View={YOUR VIEW ID}&CacheControl=1

Example URL:
http://dansSandBox.com/_vti_bin/owssvr.dll?CS=109&Using=_layouts/query.iqy& List={94234BEC-FAE1-4B4C-80E8-2AE5F9B419CB}View={A3081327-C448-47E4-A076-CE0A1BCED059}&CacheControl=1

The trickiest part of the set-up, is just to find the ID for the list and the view you would like to export. The easiest way to obtains these I feel are through SharePoint Designer, but if that is not an option for you, you can get them through the UI.  The downside of obtaining the ID’s through the UI is that they will be encoded and will need to be decoded before they can be used. This tutorial will show how to find these ID’s in designer, but I will touch on how to obtain them through the UI later in the article.

1. Open SharePoint Designer

2. Open Your Desired Site

3. Select Lists and Libraries

4. Select the List or Library you would like to create the export for

5. From the list detail page, select the List ID

a. clip_image002

6. For the View ID we will be obtaining it from this same location. To the right of the list information select the name of the view you would like to create the export from

a. clip_image004

7. Now if you are not a developer you may be scared by the code which appears, but we simply will be looking for one set of markup which is Name=’{View ID}’ (mine was located at line 46)

a. clip_image005

Again the trickiest part of this solution is obtaining these two IDs.  You can obtain the ID’s through the UI but you will have to decode the values which I find to be a larger pain than using designer which has them decoded. An easy way to get them through the UI is to edit a view on your list and scroll down to the mobile section, from her you can see the encoded URL with the List and View equals parameters.

clip_image006

Well that is it folks! Once you have created the URL you can test it right in your browser, if you set up the URL correctly you will be prompted with a dialog box similar to this one.  This dialog box prompts the user to open the data connection to the list, which by default will open in Excel.

clip_image008

The great thing about this solution is that you can apply it to a button, an image, or even create a navigation dropdown for all of your exports!