Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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).

share|improve this question
    
Sounds like a move to Access might be on the cards? –  Highly Irregular Dec 21 '11 at 22:46
add comment

2 Answers

up vote 1 down vote accepted

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?

share|improve this answer
    
I think what they mean by "background" is that you can set it and forget it, rather than in a different thread. However, that registry setting for AutoRecoverDelay might be the answer - will try it out. I use the spreadsheet in bursts so there are many periods when it is idle. The machine I'm using is a fast Dual Core with 4Gb memory and running Vista 64. –  Joe Schmoe Jul 16 '09 at 11:45
    
It actually does spawn a separate thread, unfortunately in your case that thread is doing a lot of work. –  Diago Jul 16 '09 at 11:52
    
Okay - well it must be the size and complexity of the document that is still locking up Excel despite that. Happens on all the machines I've tried it on. I'm going to mark your answer as the accepted solution - increasing the AutoRecover idle time to a couple of minutes instead of 30 seconds will suit me just fine...it's probably going to be quite rare that I leave it for that long right in the middle of a discrete bit of data entry, whereas 30 seconds is too short. Thanks! –  Joe Schmoe Jul 16 '09 at 12:02
    
Thanks - Sorry I could not be more help. –  Diago Jul 16 '09 at 12:07
add comment

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.

share|improve this answer
    
I already know how to do AutoRecover saves. The point is that the Excel autorecover locks up the document for up to a minute while it does it...hence I need to be able to do it in the background –  Joe Schmoe Jul 16 '09 at 10:39
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.