Error: SharePoint List – Cannot Edit in Datasheet, Export to Excel, or Import Spreadsheet

Several months ago users started reporting an inability to edit lists in a datasheet, and to import from or export to Excel. It was first reported in our production environment, and when we investigated, we found the problem in the dev environment as well.

Google searches were unsuccessful at finding any relevant reports of causes for the symptoms. Mostly the references were for single users experiencing the problem, but for us no one was able to perform these tasks. One page referred to Web Service Enhancements (WSE) 2, but this was not something that was relevant to our environment.

But recently we discovered that a lesser-used “staging” version of our site was not experiencing the issue! This gave us a baseline to start making some comparisons against.

Symptoms

Here is a full description of the symptoms:

When you select Actions >> Edit in Datasheet, and make changes:

(message at bottom of screen) You have errors. Resolve.
(message at bottom of screen) Read Only.
(Dialog) For this error, you can retry or discard your changes.
(buttons) Discard my changes. Retry my changes.

When you try to exit the page:

(Dialog) You have pending changes, or unresolved conflicts and errors. Do you want to wait for this operation to complete?

When trying to edit in datasheet, the user is able to change the cells, but the changes are not completed on the server.

Notice the Access icon in the upper left corner of the datasheet. The datasheet is an “Access Web Datasheet”. I spent a while chasing down what would cause that to fail to work properly.

Another symptom was in Actions >> Export to Spreadsheet:

(Dialog) Do You want to open or save this file? filename.iqy file
(Dialog) Microsoft Office has identified a potential security concern.
(Dialog) Excel cannot connect to the SharePoint list.

First message when trying to Export to Spreadsheet

Second message when trying to Export to Spreadsheet. This message is standard and not an indication of error.

Third message when trying to Export to Spreadsheet.

Another was in Actions >> Open with Access:

When attempting to Open with Access, Access would open but without any opening any databases. The accdb file for the SharePoint list would appear in the Open Recent Database pane, but clicking on it got a dialog box –
(Dialog) Could not find the file filepath\filename.accdb

When trying to Open in Access, the access file is not able to be found.

And in Create >> Import Spreadsheet:

Error message when trying to create a list from a spreadsheet import

Error message when trying to create a list from a spreadsheet import

Troubleshooting

Once we had identified an environment where these features were working, we were able to start closing in on the problem. Locating the web.config file for the working web application, I substituted the working web.config file for the one in the dev system (after backing up the current file!), and the problem no longer occurred.

To get the locations of the web.config files, open IIS, right-click on the web site and choose properties. Click on the “Home Directory” tab, and find the Local Path, which should be something like C:\Inetpub\wwwroot\wss\VirtualDirectories\######. Your web.config file is in there.

Use a file comparison tool. I started out with Windiff, but was advised by a co-worker to use Beyond Compare, and boy was that ever a good choice! With Beyond Compare, I was able to compare the files side-by-side, with the differences highlighted, and to move blocks of code from one version to the other with a single click, and undo with CTRL-Z. It could not have been easier.

After substituting the working web.config file for the one causing the errors, I then added sections of code back into the now working web.config file from the backed-up file, and tested after restoring each section.

Cause

   <webServices>
      <soapExtensionTypes>
        <add type="Cognos.Portal.Services.SoapPatchExtension, Cognos.BI.WebPart, Version=1.0.0.0, Culture=neutral, PublicKeyToken=################" priority="1" group="0" />
      </soapExtensionTypes>
    </webServices>

It was that “simple”.

I inherited the site from a previous administrator, so I wasn’t in on all of the decisions prior to my apotheosis. And there is definitely no knock on the previous guy, who is great. But there is a story to this…

Prior to his departure, he was trying to add some third party web parts (Cognos, as you can tell from the code) that had been approved for use in our environment to fill a particular business need. The implementation didn’t work, and from what I understand, he spent quite a bit of time working with the vendor to get the web parts configured correctly, apparently to no avail. The project got shelved.

It was after his departure that the problem was first reported, and, having no idea what changes had been made when (yes, I am implementing a change log), or when exactly the problem had begun…

Solutions

So for starters, since the web parts were never configured properly, the easy way out is to comment out the “add type” line, which does resolve the issue.

In the longer term, the issue will be to get the web part to play well with the other features in SharePoint, like editing in datasheets, using excel to import and export, and so on. That the web service is not Microsoft Web Services Extensions 2 per se threw me off originally, but now that I know the problem is in the webservices section of the web.config file, I will be taking a closer look at the Web Service Enhancements (WSE) 2 article, which points to using “remove type” in a “location path” in the web.config file. I’ll be experimenting with how to implement that in a way that works for our issue.

I still don’t have a full understanding of why this causes a problem, but if you are experiencing these symptoms, I hope this post helps you to find your solution.

More posts about SharePoint.