Visual Basic Editor

[msawaya]

I am trying to write a little vb program in the excel's vb editor which will save the excel document automatically. How would I go about this?

[Iouri]

Why do you need to do this. Excel already has this buil-in feature.

Save workbooks automatically as you work

1 On the Tools menu, click AutoSave.

If the AutoSave command is not on the Tools menu, you must install the AutoSave add-in.
2 Select the Automatic save every check box.
3 In the Minutes box, enter how often you want Microsoft Excel to save workbooks.
4 Select any other options you want.


To install Add-in
Add-ins are programs that add optional commands and features to Microsoft Excel. Before you can use an add-in, you must install it on your computer and then load it in Microsoft Excel. Add-ins (*.xla files) are installed by default in the Library folder in the Microsoft Excel folder. Loading an add-in makes the feature available in Microsoft Excel and adds any associated commands to the appropriate menus.


Iouri Boutchkine
iouri@hotsheet.com

[nederlof]

The Excel built-in Autosave sucks, because it does a hard save over your existing workbook, so you lose any chance to back up to a previously saved version every time the Autosave kicks in. Even worse, you also lose your 'Undo' list every time the workbook is saved. Also, what's quite annoying is that it saves in foreground all your open workbooks, taking quite a bit of time if you have some large files open, but not necessarily changing them all of the time. In comparison, MS Word's autosave is a lot smarter: it works in background and saves into a separate file somewhere unnoticable. Actually you don't even notice it until Word crashes and later magically opens with your documents recovered. I always wonderd why they couldn't implement the same sort of autosave in all Office apps.

Actually, I am currently writing my own 'Smart' autosave, that will allow you to actually keep a whole history of changes if you wish to. I'll be happy to share it on this forum once it is working properly.

This actually brings me to a question that someone here may be able to help me with: I am using the Application.OnTime method to execute an autosave and to schedule the next one. This OnTime method will wait until Excel is in ready mode, but this fails when you are debugging code in the VB editor. Evidently that is considered as a ready mode, but since you are already running something, you get the "Can't execute code in break mode" warning, and the autosave doesn't run. In my case, this also prevents scheduling of the next autosave, so it is effectively turned off.

Does anyone know of a way to avoid this? I cannot build in any checks into my own code, since it won't execute at all to begin with when I am in the debugger. Is there a way to fork off a separate process or something like that? Right now I have to revert to all sorts of unelegant alternatives like scheduling a number of autosaves or alive-checks in advance to execute, so that one failed run won't break the chain.

Something else that's nagging me is that I cannot seem to capture application events (like WorkbookBeforeClose or WorkbookBeforeSave) in a reliable way. I have done exactly what the Excel VB help says under "Using Events with the Application Object", but, especially when I am using the VB editor, it stops working from time to time for mysterious reasons, until I reassing the "X.app=Application". Does anyone have a clue why this happens?

Thanks for any info,

Leo