SharePoint 2007 Export to Excel Failure

SP2ExcelSchemaError

Cannot get the list schema column property from the SharePoint list.

Here’s an interesting problem I encountered at one of my clients: a SharePoint 2007 SP2 list which previously exported to Excel just fine was now producing the above error every time. Adding insult to injury this started happening after someone accidentally deleted a column from the list which was critical to the event receivers we created.

Having only the missing, and reconstructed, field as a guide, we found hiding the column from the view before export would allow the export to succeed. Armed with that knowledge I started searching like crazy. I found site after site telling me this happens with Date fields. The default value had been changed and to fix the error simply open the field, set the default value to something rational like 12/12/2008, save it, edit it and clear the value back out. Other sites advocated converting the Date field to Single Line of Text and then convert back.

While we have 26 date fields in this custom list, that wasn’t the column that was causing the problem. The faulting column was a lookup to another list. Finally after a few hours of searching I came across this awesome post. Specifically:

7. Lookup column points to a calculated field. Changed the lookup column to not point to calculated field but to a Single Line of Text field and the problem went away.

So terse and so right. The list from which the column got its values used calculated columns to display an office if it was the right type. The really confusing part is knowing there’s another column set up the exact same way to the exact same list but a different calculated column. Only the deleted lookup field that was recreated the exact same way it had been caused the problem.

If that was confusing, I’ll try to explain it again. List NAMES has a single line of text, choice, and 2 calculated columns. The calculated columns either contain the value of the single line of text if the choice field is the appropriate value or it is blank. The calculated columns are mutually exclusive – only one has a value per row; the other is blank.

List DATA has many fields but importantly it has 2 Lookup columns pointing to the NAMES list. Lookup 1 points to the first calculated column; Lookup 2 points to the other calculated column. Lookup 1 was accidentally deleted by person unknown. It was later recreated with the same internal name, renamed to the expected nice display name and re-populated with the old data. Oh, and promptly sealed to keep this from happening again.

When viewing the items in Data Sheet view, with the Lookup 1 column shown, exporting to Excel would fail. Hiding Lookup 1 from the view and then exporting worked just fine.

The fix was quick once I got my clue. I went to the NAMES list, deleted the Calculated column referenced by Lookup 1, recreated it with the same name but as a Single Line of Text. Then I manually copied the appropriate values. The DATA list retained all the values previously selected from the Lookup and didn’t need rebinding! (Which was good since the Lookup field is sealed.) Now the view exports like a champ. Extreme frustration replaced by bemused confusion.

Otherwise known as: SharePoint SOP.

Good humoredly,
-Erik

Advertisements

5 responses to “SharePoint 2007 Export to Excel Failure

  1. So it sounds like you didn’t really solve the problem but created a work around. I have the same exact problem, but still need to have the lookup from a calculated column in another list that concatenates several fields. Whats interesting is the fact that when you choose “allow multiple” in the lookup column, it allows you to export.

  2. That’s weird. I would expect that to be even more broken.
    You’re right in that this is just a workaround. I didn’t have time to investigate the underlying problem – if it was a Content Database issue, poor code choice…but the field schema looked correct. This was an acceptable solution for us and I had to just move on to the next SharePoint problem.

  3. My issue is I have no control of settings and don’t know where I would change it to a single line of text.

    • Without list settings control there isn’t really anything you can do. Can you reach out to a support team and point them here or just let them know what’s happening and what you need done?

  4. HI, i got the same error when i tried to export the list to excel. The thing is i dont have datatype lookup and data/time in the fields. Still why i am getting this error? Please provide the solution asap

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s