nederlof
May 29th, 2001, 12:17 PM
(I wrote this in a reply I posted last week, but actually it's a genuine question, so here it is as a new post, in case people missed it...)
I am writing my own Autosave for Excel, because the built-in Autosave sucks. 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.
So now I am 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.
I have actually two questions to this forum:
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 effectively ceases to protect.
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 until I reassing the "X.app=Application". I have put a watch on the X.App applictation property in the VB editor, and noticed that it gets reset whenever I press the 'Reset' button, or when a program encounters an 'End' statement. Though I understand that this is not too unexpected behaviour, it's impossible to avoid these resetting events while you are writing, running and debugging VB code.
Somehow, the Excel built-in autosave has come around these two shortcomings, so there must be a way. That one too is written in VB, I'm sure, because sometimes it runs into an error, and you can see lines of code in the error message that pops up.
Thanks for any info,
Leo
I am writing my own Autosave for Excel, because the built-in Autosave sucks. 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.
So now I am 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.
I have actually two questions to this forum:
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 effectively ceases to protect.
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 until I reassing the "X.app=Application". I have put a watch on the X.App applictation property in the VB editor, and noticed that it gets reset whenever I press the 'Reset' button, or when a program encounters an 'End' statement. Though I understand that this is not too unexpected behaviour, it's impossible to avoid these resetting events while you are writing, running and debugging VB code.
Somehow, the Excel built-in autosave has come around these two shortcomings, so there must be a way. That one too is written in VB, I'm sure, because sometimes it runs into an error, and you can see lines of code in the error message that pops up.
Thanks for any info,
Leo