In order to connect to an Excel file in a SharePoint 2010 and access or manipulate elements via web services, the file must not contain any unsupported features, in terms of Excel Services.

This can be demonstrated via a simple test program which interrogates SharePoint 2010 via a web service and returns all named ranges in an Excel file.

Where the file contains no unsupported features, the named range is returned:

This web service connection in SharePoint 2010 now functions even if there are macros in the file, and it is saved with an xlsm extension, unlike in SharePoint 2007.

 

However, once there is an unsupported feature in the file, in this case a Drop-Down List for data validation, the file can no longer be accessed via web services.

 

 

To download the test program, click here.

Advertisements