Thursday, October 11, 2012

Excel Data Validation as an unsupported feature in SharePoint and the perils of hidden worksheets

Several, if not all, of my posts so far have been based on SharePoint.  We have several teams where I work that publish and analyze reports, and several others that rely on the data of said reports.  As such, Im tasked with taking much of our business data and making it more "web enabled" (what a novel concept!).

So I moved one of our report's into SharePoint, which I received the following error (side note.. wouldn't it be great to validate the file before publishing it Microsoft?)

The workbook cannot be opened because it contains the following features that are not supported by Excel in the browser:

- Data validation

Would you like to try and open this file in Excel?
OK, after a few minutes of Googling, I found that there's a command to find data validation conditions.  In each workbook, choose Find and Select from the Home tab, Go To Special, and Data validation.  If the search finds something, it'll move to the appropriate cell, otherwise you receive an alert that there is no validation on the worksheet.

Once you happen upon your cell with validation, choose the Data tab, click Data Validation, and choose clear all.

Just repeat the steps listed above for each worksheet to remove all your validation.

Make sure you scrub ALL your worksheets..

Upon completing these steps, I published the file again, but still receive the same error.  I re-checked for data validation, published as different file names, restarted IIS, anything thinking it wasn't my excel spreadsheet... because I couldn't find the data validation anywhere else. 

Then, I found out that there were HIDDEN worksheets in my workbook too.  I right clicked the worksheet area, clicked unhide, and chose the hidden worksheets.  Wouldn't you know it, the hidden worksheets also had data validation.

Simplify, simplify, simplify:

So in closing, I learned the following:
  • You can "find" data validation in Excel via the Find and Select command
  • You need to "guess" if worksheets are hidden, and be sure to unhide them when removing any type of "unsupported features" for Excel Web Access in SharePoint
  • When publishing a workbook to SharePoint, the unsupported features must be removed from EVERY worksheet\item\range in the file, not just what you are choosing to "publish".

6 comments:

  1. Without removing data validation can we view the workbook in web browser

    ReplyDelete
    Replies
    1. It's been a while since I went through the above exercise. I *think* the workbook still loads, but the error message about unsupported features will display. If the user were to close that message, they can view the workbook... but the data validation certainly won't work.

      Delete
  2. Very interesting article and as an extension, if you need to structure all of your records and ensure the protection of documents, I would like to draw your attention to the cloud server secure virtual data room

    ReplyDelete
  3. Thank's for your advices! But if somebody can not cope up with it for some reasons, I reccoment to you one way to open .csv file https://wikiext.com/csv. Because I had some problems, but I was able to fix this, with the help of this website. Who knows, maybe to some body this information will be useful.

    ReplyDelete
  4. I felt very happy while reading this site. This was really very informative site for me. I really liked it. This was really a cordial post. Thanks a lot!. for grammarly trial click here

    ReplyDelete