Embedding Live SharePoint Data into an Excel Spreadsheet

[This post courtesy of Chris Almida]

Windows Small Business Server includes Windows SharePoint Services by setup by default.  We use SharePoint daily at Microsoft to collaborate as a team.  One of the greatest benefits seems to be sharing a spreadsheet or List of items.  The beauty of SharePoint is multiple people can work on it at the same time.  Unlike with Excel, if you put it on a share, only one person can open it in Read/Write to make changes, or if you’re e-mailing it around, knowing which version has all the information in it.

With a SharePoint List, multiple people can work on different sections of a spreadsheet, but this causes the problem of what if you want the power of Excel behind your data to do graphing, or calculations on that data?  Well, you can build an Office Connection to download the latest data each time the spreadsheet is opened!

It’s super straight forward, here’s how (at least with SharePoint v3 UI):

On the SharePoint site

1. Navigate to the list you want to update from within Excel.
2. In the top right-hand corner of the sheet drop down the View box and choose Create View.
3. In the page that shows, choose a Datasheet View.
4. Give the view a friendly name so you can find it, for this demonstration let’s just call it “Datasheet View”, and click OK.
1. Feel free to scroll down and apply any filters to strip data out depending on the purpose of the spreadsheet you’re building.
5. Once you’re looking at your new Datasheet view, you want to go to Actions and then choose Task Pane. A task bar will open on the right (you can also use a little gray arrow on the right if you want).
6. click Query List With Excel, and Excel will automatically open

In Excel

1. Choose to Enable the Data Connections (as for security reasons they are typically blocked, but we trust our Internal SharePoint site, so it’s ok).  Once you Enable the data connection, all that Data goodness from SharePoint comes flying into Excel, ready to be worked with.  If you wanted a one time connection, you’re done and you can stop now.  If you want to create a connection you can use over and over again, continue on.
1. If an Import Dialog shows, make sure it says New Workbook at the bottom
2. Let’s go and Export the Office Connection so we can use it over and over again
1. On the Data tab of the ribbon, click on Connections
2. On the Work Book Connections pop-up, you’ll see a randomly named connection, ensure it’s selected and click Properties
3. Give the connection a friendly name under Connection name as I have done above.
4. Select the Enable background refresh and Refresh data when opening the file.
5. After you’ve made these changes, switch to the Definition tab and at the bottom, click Export Connection File.  Save this file with a well known name in a well known place.  I use the Connection Name I defined earlier as the file name as well, then click OK.

Using the Saved Connection in a new Excel file

1. Now that you have the connection to SharePoint saved as an Office Connection, simply open a new or existing excel spreadsheet, and select the A1 cell (the headers will come in with the connection, straight out of SharePoint!
2. Click on the Data tab and click Existing Connections and then Browse for More … and navigate to the saved Office Connection.
3. On the Import Data  select OK, and the Data from SharePoint just seamlessly Jumps into the Spreadsheet.

Now every time you open that Excel file, it will re-query the SharePoint site for the latest data.  Updating data only in one place saves time and energy.  You can program Excel to make calculations on the data, graph the data and show the data in different pivots as you see fit within Excel.

As a side note, my manager uses this remote data methodology to also download tables from the Internet for his fantasy baseball league to help him make his weekly picks!  If anyone generates a spreadsheet as sophisticated as his, let me know….. No, I can’t give his out, because I don’t have it!