At one of my customers we have been strugling for a while with a problem that occurs when you try craete a new list by using the Import spreadsheet function.

Once again it was one of those problems that are hard to troubleshoot since it does not happen to all users, it happens sometimes and not on all our farms. This is usually indicating that there is something wrong with the client or the network like firewalls or load balansers, but you can never be sure. However, this time it seemed to be.

What happens when you try to import the spreadsheet is that Excel (in this case Office 2003 but we also had it on the 2007 version) gives you a “Cannot connect to the server at this time. Your list cannot be published” when you click on import.

There are a number of posts on the topic if you google it and it seems like there is a lot of things that can cause this problem. I therefore would like to have a word about troubleshooting the Import spreadsheet.

 

On the client side

First of all, the window that handles the import commes from the Excel client and not some SharePoint thing. The file that is used is the EXPTOOWS.XLA found in Program files\Microsoft Office\OFFICE11\1033 (If you have Office 2007 then the folder will be office12)

Instead of writing it all down I’ll give you a couple of links that shows what you need to change:

http://blog.sharepointhosting.com/Lists/Posts/Post.aspx?List=399ed944%2D72d2%2D4932%2Dbc13%2Dde0586abf2d1&ID=64

http://rdacollab.blogspot.com/2008/05/importing-excel-spreadsheet-to.html

Or a thread on the MSDN forum with a lot of info:

http://social.technet.microsoft.com/forums/en-US/sharepointgeneral/thread/75b2be6b-44df-4389-bf34-c7a01c12372c/

 

We can draw the conclusion that when mixing different Office version we can get a problem with the EXPTOOWS.XLA.

You should also make sure that you have the latest patches. Especially if you are running Office 2003. You atleast need to have SP3 and the following patches:

http://support.microsoft.com/kb/939269
http://support.microsoft.com/kb/952608

If you have Office 2007 try this one:

http://support.microsoft.com/kb/956510
What also is good practise in this kind of troubleshooting is to remove old cache files and entries. Specifically in the following locations:

Windows XP:
 c:\documents and settings\[Username]\nethood

Windows Vista
c:\users\[Username]\nethood

And you should also delete the cache files located in:

Windows XP
c:\documents and settings\[Username]\application data\microsoft\web server extensions\cache

Windows Vista
C:\Users\[Username]\AppData\Roaming\Microsoft\Web Server Extensions\Cache

In addition, remove all entries in the registry location:

HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Common\Internet\Server Cache

 

When you have completed that, make a reboot of your client.

 

Finally check in Internet Explorer so that the Option Check for server certificate revocation in Internet Options ->Advanced-> security is not checked, if it is, uncheck it and restart IE.

 
So let’s move over to the SharePoint server side

Check that you in IIS has a HTTP header for SharePoint. You find this by right click on the web site in IIS and then click on the tab HTTP Headers. You should have something like:

Custom header name
MicrosoftSharePointTeamServices

Custom header value:
12.0.0.6219 (or the version number of SharePoint installed)

You should also check so that not the ASMX extension itself is missing from the list of Application Mappings in IIS. You find it in the Properties of your IIS site -> Home Directory -> Configuration. 

You should do a IIS Reset after this change and you need to do it on all your web front end servers.

 

The network

In our case we found out that the problem was related to the load balancer. We have a couple of WFEs and when we changed the stickiness type to source ip instead of cookie it started to work. What it does is that it makes the Layer type to change from Layer 5 to Layer 4, and apperently this is a known bug in Cisco CSS Load Balancers. We found this after reading an excellent post from the MSDN guys:

http://blogs.msdn.com/vsofficedeveloper/pages/SharePoint-Cisco-CSS-Switch-Issue.aspx
If you are using ISA server or any other network boxes between the server and client you need to check them as well.

Hope this gives you a good summary and that after you have read this you get rid of the problem. However, if I have missed anything please post me and I will update this post.