Is there any way of making Excel 2007 do BACKGROUND autorecover saves like MS Word does?

Question

I have a huge Microsoft Excel document - it is currrently about 25Mb in binary format (.xlsb)...the standard Excel format was too large and slow - but is still growing at a steady rate. (It has probably 1000-2000 worksheets and more get added daily)

I use this document most of the day (in bursts of fast data entry) and, for various reasons, chopping up the document into smaller Excel files is simply not an option.

Excel 2007 copes with all of this pretty well and is a perfect fit for what it is being used for, apart from one thing...Autorecover saves.

Is there any way (or any add-in) to force Excel to save AutoRecover versions in the BACKGROUND, like Microsoft Word does?

At the moment, if I am in the middle of some data entry and an AutoRecover save kicks in, I can be sitting around for about 30 seconds to a minute while Excel is locked-up waiting for it to finish...which can be rather frustrating since it breaks the flow of thought and interrupts the data entry.

I would like to avoid increasing the AutoRecover save interval...I can enter a lot of new data in the default 10 minute autorecover save interval and the data would be painful to recreate once entered. Ten minutes of data loss is probably about bearable.

The data in this document has been collected over a number of years and there are a large number of complex VBA macros that manipulate it. Moving it all to another piece of software or platform is simply not an option at the moment without a vast amount of work.

(For commercial reasons, I cannot discuss what the document contains).

Answer 1

According to this knowledge base article AutoRecover is also triggered by making changes and leaving Excel idle for more then 30 seconds.

So far every document on the process I have read mentions that AutoRecover is already done in the background, since from my own experience I know it is, so the reason it is slow I can only assume has to do with the size of the file and the location it is saving to.

Unfortunately apart from extending the time limit I can't think of any other way in your particular case that you can speed up this process.

Out of interest, I would assume you have quite a beefy machine for doing this work, since more memory might make a small dent in the process?

Answer 2

Excel

1. Click the Microsoft Office Button, and then click Excel Options.
2. Click Save.

3. Select the Save AutoRecover information every x minutes check box. In the minutes list, specify how often you want the program to save your data and the program state.

   TIP The amount of new information that the recovered file contains depends on how frequently a Microsoft Office program saves the recovery file. For example, if the recovery file is saved only every 15 minutes, your recovered file won't contain your last 14 minutes of work before the power failure or other problem occurred.

Optionally, you can change the location (specified in the AutoRecover file location box) where the program automatically saves a version of files you work on.