Monday, January 05, 2009

Microsoft has a dirty SharePoint secret

At work, like probably a lot of people I am running Windows XP with Office 2003 and Sharepoint 2007. I have been battling a problem with Sharepoint and Excel for some time now. It all centers around the Datasheet view in SharePoint. This seems like a useful feature that lets you manipulate Sharepoint lists as if they were a spreadsheet. If only it would work reliably.

When I try to use Datasheet View I get an error telling me that "The list cannot be displayed in Datasheet view for one or more of the following reasons:

  • A datasheet component compatible with Windows SharePoint Services is not installed,
  • your browser does not support ActiveX controls,
  • or support for ActiveX controls is disabled."

It seems I am not alone with this problem. Microsoft Help and Support does tell us that you have to use Microsoft Office 2003 or 2007 Professional edition. That is the first open dirty secret. With all the differing versions of Office that were sold you have to use the Pro edition to get the necessary activeX components to enable this functionality. So, if you are a small business and purchased Office Small Business Edition for your desktops and then went and bought the Microsoft Small Business Server with SharePoint then you are probably going to be stung for some upgrades.

The other dirty secret is more difficult to fix. It appears that SharePoint can't cope with you running multiple editions of Office. So, if you upgrade to Project 2007 or one of the other newer versions of Microsoft's Office productivity suite but don't update the core Office 2003 Professional version then you will probably find that the datasheet view in SharePoint stops working for you. It appears that latest owssupp.dll file (and possibly other dll's) are not backward compatible. This hybrid situation where part of Office 12 (ie. Office 2007) gets installed alongside Office 2003 is a situation that Microsoft can't cope with. I also saw this when I installed a trial version of Sharepoint designer. When the license expired the datasheet view stopped working and when I uninstalled Designer it didn't fix the problem.

With a growing dependence on web-based applications the datasheet view error is a nasty one because it actually causes Internet Explorer to crash. If you use Firefox with IE Tab it will also crash Firefox.

Right now I am running with Outlook 2007 and Office 2003 and I am wondering if this hybrid setup is a continuing cause of the problem.

I know it is in Microsoft's best interests to push us to upgrade to everything 2007 but they should be able to support their own stable of products that span across versions. Maybe this just indicates that not only is the Windows platform creaking but the Office System has also got to large and unwieldy.

It is going to be a common situation, particularly in this economy where organizations may upgrade parts of the suite in order to access enhanced functions but leave other parts of the Office suite in place on the prior version. I think Microsoft's decision to radically change the user interface in Office 2007 may make more companies reluctant to change because of the retraining costs that they will incur.

Telling us to upgrade everything to 2007 is not an acceptable answer. However, when Microsoft tells their customers who bought 30Gb Zunes to just wait 24 hours to fix their the leap year bug that stopped the devices working on December 31st, you know that we shouldn't hold our breath waiting for a fix. All this just confirms my thinking that simpler, browser-agnostic, web applications have a bright future.

UPDATE - I may have worked a fix for this problem. This is what I did:

I did a search on my C: drive for owssupp.dll.

I found two versions in these directories:

  • C:\Program Files\Microsoft Office\OFFICE11
  • C:\Program Files\Microsoft Office\Office12

Since I am using Microsoft Office Excel 2003 (ie. Office 11 Pro) I renamed owssupp.dll in the Office12 folder to owssupp.dllx.

I then restarted my PC. So far I seem to be able to get the datasheet view working. It will be interesting to see if anything is broken in Outlook 2007.

7 comments:

  1. Insightful post!!!

    ReplyDelete
  2. Anonymous7:19 PM

    Great article...thanks!

    I found a workaround that I think compliments it: add the registry key listed below. When I did it on an Office 2003/Project 2007 computer, it at least granted read-only access to the list. From there the user can select a non-datalist view.

    Note that I got the idea from this post; in that case, removing the key fixed the problem for them, but in our case the key wasn't there to remove.

    (Caution: Create a restore point and/or backup before messing with the registry...I'm not responsible for what you do to your own registry, etc etc)

    [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Internet Explorer\ActiveX Compatibility\{65BCBEE4-7728-41A0-97BE-14E1CAE36AAE}]
    "Compatibility Flags"=dword:00000400

    All the best,
    Joel

    ReplyDelete
  3. Anonymous8:15 PM

    We solved this issue (Office 2003 SOE with Project 2007) simply by opening Excel 2003 and performing a repair. Excel 2003 > Help > Detect and Repair (may require a restart). This re-instates control of the Office Web Component (runs Datasheet view) from Access 2007 back to Excel 2003. Not one of Microsoft's finest moments on backward compatibility front.

    ReplyDelete
  4. Anonymous1:59 PM

    A detect and repair failed to resolve the issue.However this seems to be an issue with Microsoft updates which restricts Activex usage on Excel and Microsoft is yet to come up with a workaround to it !

    ReplyDelete
  5. Anonymous9:55 AM

    Thanks for the 'Update' post it was very helpful for me. It pointed me in the right direction, as my setup was slightly different in that I work on Excel 2007 and run Visio and Access in 2003.

    Great work... cheers.

    ReplyDelete
  6. Anonymous8:17 AM

    Renaming owssupp.dll fixed my issue. Thanks!

    ReplyDelete
  7. Great to hear that it fixed your problem

    ReplyDelete