The problem

Since Excel 2003, users had the possibility to synchronize data lists thanks to a data source provided by a web service. This feature was introduced in particular with SharePoint and its custom lists, SharePoint allowing to directly export the lists to Excel.

Synchronization from Excel 2003 is made possible thanks to the Internet Query Files (".iqy"), which allow reading and writing in direct relation with the data source.

However, in Excel 2007, writing in synchronization is not possible by default anymore. Indeed, this feature was inhibited, as Microsoft made the choice to privilege data updates through Access 2007 or VBA solutions. As a consequence, a user who only has Excel 2007 and who uses such an iqy file will not be able to synchronize, as the menu disappeared.

Nevertheless, the ability to directly edit and update data from Excel brings a real added value to customers we've met: massive data type-ins, calculation formulas usage, data replications through drag and stretch, and many more. Therefore we consider the loss of this feature, feature to which some users are particularly used to, as a real issue. Hence, SoftFluent, decided to find a solution to let the users keep that feature, and moreover in a transparent manner.

After having deeply investigated this issue, we noted the feature was still available in Excel 2007 but requires the user to run in the "Excel 2003 compatibility mode". In order to retrieve automatically this feature, we "simply" had to automate the mode switch during the usage of synchronized lists. By the way, an entry in the Microsoft Knowledge Base was just added (see KB930006 as we were writing these lines).

To ease the user experience, we automated the mode switch in the "WSS Excel 2007 Tables Synchronizer" component, which we will describe below.

This component was developed by SoftFluent as it covers a critical importance from our point of view. Actually, SoftFluent is the creator of CodeFluent, a model-driven software factory which allows the creation of fully functional and reusable .NET components (data bases, business classes, web services, ASP.NET controls, web application, and Excel lists). To our customers, Excel lists, are a real popular mean to update their application data, and in particular their back-office data.

First of all, let's recall the user experience of the list synchronization feature with SharePoint lists.

List user experience with Excel 2003

After exporting the list from Windows SharePoint Services to an Excel 2003 spreadsheet, you can synchronize your data like this:

• Right click on the list
• Select List - Synchronize list

The list will then be updated.

List user experience with Excel 2007 without the component

In the same way, by right clicking on the table and then selecting "Table", we clearly see that the feature has been inhibited as illustrated hereafter:

You can see the feature is not proposed in the Table menu.

"WSS Excel 2007 Tables Synchronizer" installation and the new Excel 2007 user experience

"WSS Excel 2007 Tables Synchronizer" installation

The component is deployed through an ActiveX which is signed by SoftFluent. It is available here: http://www.softfluent.com/wsslists.htm, where you will be proposed to install it directly on your machine. You will then have to accept the component's installation and your set to go.

In fact, as soon as it is installed on your machine, no extra configuration in Microsoft Excel 2007 or elsewhere is required to benefit from the synchronization feature.

A transparent Excel 2007 user experience

The component will now allow uninhibited writing synchronization in Microsoft Excel 2007. Once the component installed, as soon as an ".iqy" file is launched, the "WSS Excel 2007 Tables Synchronizer" component will automatically be launched too. It will then start your Excel 2007 and create a table with the reading and writing synchronization permissions.

How it works

"WSS Excel 2007 Tables Synchronizer" is a component emulating Excel 2003's behavior for Excel 2007 regarding the SharePoint list synchronization feature via ".iqy" files.

The component is bound to files with the ".iqy" extension, which means the synchronization feature will not be available if the user doesn't use an ".iqy" file.

In other words, if you open an already saved SharePoint synchronized list in Excel 2007, you won't be able to synchronize your list as no ".iqy" files were involved in the process. The synchronization feature will be inhibited in Excel 2007, the following message will appear:

Before you installed the component, a file of the ".iqy" extension, is associated with Microsoft Excel:

After the installation, an ".iqy" file is associated with synchronization component ("WSS Excel 2007 Tables Synchronizer").

This illustrates perfectly that the ActiveX is only used as a deployment method, which deploys the only Dynamic Link Library (i.e. dll) of the "WSS Excel 2007 Tables Synchronizer" component.

When an ".iqy" file is launched, the component starts Excel, and transmits the required information to open a workbook. Then, it will add a SharePoint list in the desired mode (reading/writing).

The executed dll takes care of parsing the ".iqy" file, so it can transmit the needed information to open a SharePoint list in Excel:

• the SharePoint's application url to which the feature needs to connect to
• the name of the list to add to the worksheet
• the list view

Going further with Excel lists thanks to CodeFluent

For developers it can be interesting to understand how Excel lists can be used to provide collaborative solutions around application data. This is what we will shortly discuss in this part, through what we are setting up in our software factory: CodeFluent.

CodeFluent allows realizing multi-layer business application, conforming to the SOA model, from a simple abstract description of the business model. CodeFluent can generate web application, rich-client, floating or centered around Office, and in particular Excel. In that last scheme ? often used combined with other user interfaces ? Excel becomes the font data manipulator thanks to the synchronizable lists.

Let's consider, as an example, a car rental business application. This sample model named CarRental contains entities such as cars, clients, agencies, contracts, and so on. Then, the application exposes these entities in the form of a list like SharePoint views. Those views can be made accessible through a web site with links to ".iqy" files, which will provoke a SharePoint list like behavior.

We then get the exact same scenario as explained earlier, such as the behavior of a SharePoint list in an Excel spreadsheet.

Thus, lists created by CodeFluent allow you to export your data to a spreadsheet and type in data just like in a SharePoint list. This lets you handle the same scenarios (offline mode, massive type-ins, spreadsheet calculations, dynamic pivot tables based on the list) but using a link to an applicative data base and its business layers associated (including business rules).

Thereby, the "WSS Excel 2007 Tables Synchronizer" component allows you to keep the CodeFluent lists benefits inside Excel 2007 in a transparent manner.

Naïla ZEROUG