Excel Files Keeps Corrupting

[paulrockliffe]

I have 5 .csv files being created by a database process. I have a separate Excel file that is used to analyse the 5 files. The 5 .csv files are loaded into the Excel Data Model (Excel 2013) and Relationships are established between the common column so that all data is available when building Pivot Tables from the Data Model.

The analysis has to be in Excel, so this method is really about automating refreshes of the underlying data. By over-writing the .csv files and running Data -> Refresh All the Excel file updates itself. I know this isn't a great method, but I'm very constrained, so alternative approaches aren't possible.

Anyway, there are 10 different versions of the data, so I've been building the 10 spreadsheets by running the Update All, pointing the Connections to a different folder and saving the result. This has worked up to the 7th spreadsheet, but now Excel is doing something odd.

I create a copy of my file under a different file name and open it. It works perfectly and has the right data in. I then update it and change the connections to the .csv files in a different folder and all the new data is loaded into the Data Model. The spreadsheet works perfectly and contains all the new data. I then save the file and close it. When I reopen it I get a message saying that Excel cannot open the file because it is corrupted.

The file isn't trying to update the data, the updates are manual only and nothing has changed since the first 7 spreadsheets created in the same way were saved. All 7 existing spreadsheets will open without problem, but none of them will work as a template for the three I still need. I've tried rebuilding the data from scratch in case there was an issue with the .csv files, but that has had no impact at all.

More often than not once I've had the corrupted file message Excel hangs and I have to kill the task to get Excel working again.

I'm at a complete loss as to what has happened to stop the process I was using to build the templates from working, so any thoughts or advice most welcome?

Thanks

 

[ranman256]

sometimes its a false message...
if you save an excel file in old format (.xls) but use the extension ,.xlsx,
XL will give the error.
But if you rename it back to .xls, it opens just fine.

 

[paulrockliffe]

Thanks, I'll try that and see what happens. The file is actually an .xlsm as there's a small bit of code that opens the file in fullscreen mode, but I might as well try anything and see what happens.

It doesn't explain why I was able to get most of my files setup this way and the process has suddenly stopped working though.

 

[paulrockliffe]

OK, so I've sort-of fixed this issue. Well I've identified the cause and removed the issue; I tried rebuilding the template from scratch and saving-reopening after adding each table. It corrupted after I added a Pivot Chart that (until you filtered with a slicer) contained something like 10,000 columns for 5 fields. I've deleted that chart and a couple of other similar chart and now I can carry on my process and build all my templates and (fingers crossed) they'll update automatically when I rerun the project in a few months time.

A bit annoying that I've had to kill the charts, but not a show stopper. Interested to know if anyone knows why this is a problem, beyond it being a lot of data? Excel does happily render those charts and quite quickly when the slicers are used, just doesn't seem to be able to update them.

I'm going to test whether connecting the charts to the Pivot Tables instead of to the Data Model will work when I have a chance.