Friday, February 13, 2009

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 image

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 image
    2. On the Work Book Connections pop-up, you’ll see a randomly named connection, ensure it’s selected and click Properties image
    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.image
  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!


Dean said...

Can a previously created complicated excel spreadsheet be imported into Sharepoint V3 and then be accsessed by Excel later by different people using this method ?

Sean Daniel said...

The data portion of the excel, yes.

Dean said...

Thanks, can you elaborate.

Dean said...

Also, can it be Excel 2003 or does it have to be Excel 2007 ?

Sean Daniel said...

Hi Dean,

These steps are outlined for Excel 2007, I'm not sure if Excel 2003 can do this, but I believe it can. I'm sure it's called out clearly in Excel Help documentation. This was more of a heads up and "here's how to do it with the latest" steps. If you're interested in Excel 2003, I'm sure you can search the help for "data connections" and find something of use.

Clayton M said...

Great article, thanks. In your experience, would this work in a hosted environment? For example, if a client was using hosted sharepoint (ie an off-premises server) but had a local file server, would they be able to setup the same Excel data connection between the local Excel file and the hosted Sharepoint list?

Sean Daniel said...

I don't see why it wouldn't. Excel can grab all sorts of data from the web all over the place. I'd just make sure you're using a secure connection (SSL) to connect to the hosted solution, unless it's public data, like baseball stats. :oP