With enough opens and closes I eventually managed to get one that came up where the Excel reports were hosed but the PowerPivot data was still there. I made notes of all the tables and relationships, then rebuilt the file from scratch. Fortunately it was an easy one.
I tried a few fixes that Rob Collie suggested, including:
I have uploaded the file to Microsoft for them to look at. Hopefully they can figure out what happenend and improve their error/recovery algorithms out of it.
- Zeroing out item1.data as described near the end of this post http://bit.ly/g15XvS. Then trigger the PowerPivot recover process (as compared to the Excel recovery process)
- Unfortunately the file still crashed on opening, without PowerPivot's recovery process ever kicking in.
- "Transplant" item1.data into a blank "host" workbook. Create 1 pivot against the model, save file. Then open the crashing file in "safe" mode and try copying sheets over to new wkbk one at a time.
- Transplant was successful, but the file kicked off a recovery upon opening. The PowerPivot data was not present, even if I tried to copy the _rels files as well
- In addition, the original workbook still crashed Excel even if trying to open it in safe mode
The good news is that I was able to make the file MUCH more efficient than I did when I originally built it.