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.