Better with Office - Data Connection Libraries

Previously in this series, I’ve talked about general Office documents, which might be Word files, PowerPoint slide decks and so on. In this post, I’m going to go a bit more specific. I’m going to talk about data connection libraries and how they are they can accessed using Excel 2007.

A data connection library, as the name might imply, stores data connections. But what do I mean by data connections? Let’s say that you’re creating a spreadsheet that using information from somewhere else. Maybe you’re using Excel to make graphs of sales trends based on figures in a company database. Maybe you want to use SharePoint and Excel’s capabilities to create a visual representation of stock levels in a particular location and tie in a workflow for restocking. Whatever the data is, the point is that you have information stored somewhere that you want to use in Excel. You probably don’t want to manually copy the data over because this is time consuming and would mean you’d have to manually update your spreadsheet whenever the data is changed.

So, you create a data connection. This links your spreadsheet to Access, a web source, a SQL databases, an Analysis server report or a few other options. This allows the data to remain centrally stored but you still get the ability to analysis, view and visualise using Excel’s capabilities.

 

These data connections are part of Excel’s Business Intelligence offering. My focus is SharePoint, so I’m not going to go into too much detail about how to set up and use these connections. What I am going to talk about are data connection libraries, which are a feature of SharePoint 2007.

The idea is that more than one person is likely to want this data. If you have a database or a SharePoint list storing information that people are going to create connections to, there’s no point each of them having to go through the process of setting up the connection. It’s always possible people will link to the wrong table and bring back incorrect data. Instead, one person, who knows exactly which table or list is the relevant one, will create the data connection and put it in this SharePoint library. From then on, users will be able to access this data connection and don’t have to worry about setting up their own.

There are a few different ways to add a data connection to this library. The first is to simply upload a pre-made data connection file.

 

If you have a data connection already set up for a spreadsheet, you can open up the properties for this and choose to export the connection. You can then save the connection file into the SharePoint library.

 

Finally, if you’re connecting to a SharePoint list, you can save it to the library when creating a connection from SharePoint. In a SharePoint list, there is the option to export to spreadsheet from the actions menu. Choosing this option will create the necessary data connection and, at this point, you have the option to the save the file and can choose the data connection library as the location.

Once you have your data connection library populated with this connection files, users can access them from Excel. From the data tab, users should click existing connections then to browse for more. In the browse window that opens, users can go to the address of the data connection library and pick the correct file. If you’re going to be putting a lot of connection files into this library, it’s absolutely vital that you name your files clearly, otherwise users won’t know which is the correct connection.