Create archive Word/Excel files at save? (Word/Excel 2000)

Topic

New Reply

I’m trying to find a way to archive previous versions of Excel and Word files as they are saved; as in, when a file is edited and then saved, the *original* version of the file is copied to another directory, so that we have a running history of changes. This needs to apply to every file that is opened, which I think means using application events — and leads me onto very shaky programming ground, as I’ve never come to grips with events yet and don’t have a good sense of the possibilities. I’m hoping someone here can tell me if I’m trying to do the impossible.

It seems to me there are two possible (?) ways of going about solving this:

1. At the point in time when the file is saved, use the BeforeSave event to make a copy of the original file to an archive directory.

2. At the point in time when the file is opened, use the Open event to make a temporary copy of it; when the file is saved, move the temporary copy to an archive directory. If the file is not saved, delete the temporary copy.

Option 1 looks more efficient, because nothing is done at all if the file is never saved. But it depends when the BeforeSave event fires, and if it can truly make a copy of the original file without edits. I think that it’s possible with Word (because it makes its own temporary copy of the file for editing, and then copies it back over the original file at save), but I’m not familiar enough with Excel to be confident in how it manages files — and Excel is the more important program in this case.

Option 2 seems to me more likely to get a true copy of the original file, because it makes a copy before the file can be edited — but there’s extra overhead involved because it makes a copy every single time a file is opened, whether it is saved or not. Then those temporary files always have to be dealt with, either by moving or deletion or whatever.

Has anyone ever done anything like this? Does either of these methods seem more likely to work — or am I overlooking something better? I’m really clueless at this point, so any guidance would be greatly appreciated.

Thanks!

Reply | Quote

Replies

Word has a built-in backup option: select Tools | Options… and activate the Save tab. Tick the “Always create backup copy” check box, then click OK.

For Excel, you can use Pieterse‘s free AutoSafe add-in from JKP-ADS Download page.

Reply | Quote

I knew about Word’s backup option, but it’s not exactly what we were looking for, because it only saves one version back. I’m looking for a way to save every past version (i.e., if the user saves 30 times, then I should end up with 30 archived copies of the file).

I will have a look at the Excel utility. Thanks!

Reply | Quote

(Edited by jscher2000 on 03-Aug-05 10:09. Fixed egregious typos.)

Word probably won’t let you move a copy of the file before replacing it, but you could use a double “Save As” to save into your archive folder and then re-save into the active folder. To examine a sample FileSave macro that inserts a numeric count into a document’s file name on each save (up to 999) see post 283508.

Reply | Quote

> I’m trying to find a way to archive previous versions of Excel and Word files

I’m doing this for a client right now. Also PPT, LotusNotes attachments etc.

I have found the Application/Document events to be of little use, which surprises me.

I have found that macros for the commands “FileClose; FileCloseAll; FileCloseOrExit; FileExit; FileSave; FileSaveAll; FileSaveAs; FileSaveAsWebPage; FileSaveFrameAs and FileSaveVersion” give me the most hope.

I have found that slave functions titled “CloseOneDocument(Doc As Document); SaveOneDocument(Doc As Document) and strSaveWithNewName(Doc As Document) As String” give me the versatility I need.

I have also found that Word is quite gracious in detecting a FileSave on a never-saved document and treating it as a FileSaveAs.

I’d like to suggest that you write short macros with the titles above, each macro having a MsgBox”FileClose” type of body so that you can get a feel of who calls what where and when. Sometimes the Why evades me (grin!)

Our user base is reasonably intelligent, capable of coping with decisions etc. We have elected to save when the user elects to save, rather than at open, since users are savvy enough to want to open a document and tool around inside looking for text, with no intention of having opened the file for major surgery. Our GUI form has three command buttons; the ubiquitous “Cancel”, “save” which is effected as “save with all our fancy footwork” and “ByPass” which means “Get me out of here and pop me into the native FileSaveAs dialogue box”.

Hope this helps.

Reply | Quote

[indent]

---

I have found the Application/Document events to be of little use, which surprises me.

---

[/indent]

This surprises me a bit too, because I have gotten a rough version of this working in Excel using the WorksheetBeforeSave Application event. Though admittedly, (A) your solution sounds a lot more comprehensive than what I am working on, and ( my code is only half-baked at this stage, if that.

Can I ask what kind of issues you’ve encountered using the Application events in Word? I had hoped that my Excel code would convert relatively easily to Word, because most of what I’m using is common to both — just a matter of changing “Activeworkbook” to “Activedocument”, etc.

I will keep in mind intercepting the Word commands as another potential route to explore — and thank you for the list of relevant commands to intercept, I would definitely have missed some of those.

Thanks!

Reply | Quote

> I have found the Application/Document events to be of little use

As usual I went off half-cocked ….. I should have qualified that; I found them less useful than I’d anticipated FOR THIS JOB! I’m having a barrel full of fun with the document & application events for MRU handling, both for an application that maintains a lengthy MRU list (better than MSWord’s limit of 9 entries) and an application that loads MRU folders at the top of the MyPlaces (left hand side of the File Open dialogue). The best part is that these aps merge the Excel & Word events, so I can open a MRU excel workbook from the Word list and vice versa!

> Can I ask what kind of issues you’ve encountered using the Application events in Word?

In terms of the client ap., the main issue was that I wanted to know what the user was doing before I took action. A generic “save” event didn’t tell me as much as a FileClose or FileSave event. With the Document.Save event I felt that I was being told to save the document, without knowing why, but with FileCloseAll macro I knew that I could prompt the user “Do you want to save this file?”, and so on, prior to instituting the save. (We also have a better msgBox that asks ” Yes, Yes ToAll, YesToAllIfMoreRecent, No, No ToAll, NoToAllIfMoreRecent, Quit, RollBack? “)

> I had hoped that my Excel code would convert relatively easily to Word

Ah, you see, I’m lazier than you.
I wrote a single GUI form with a single chunk of code to manipulate names (we have a special format for names of files), and I invoke that from any MS application and also non-MS. Lotus Notes, for example, merely hands me a single string parameter being a list of file names (Lotus Notes attachments) that are to be moved, and my MSWord code coerces the name to our format and effects the move. One chunk of code, and it doesn’t matter who/where/what wants to use it – it’s working.
Less maintenance, too.

>I will keep in mind intercepting the Word commands

I found it most helpful to draft two separate templates, one with the intercepted commands (FileClose macro et al.), and a second independant template with event handlers.
I placed one in my startup folder and watched what got intercepted (without effecting the save, close etc, just the MsgBox popup).
Then I played with the other.
Watching the msgbox popups gave me a clear idea of what would suit best for my specific task, and the whole demo experiment took under 30 minutes to code and run.

Reply | Quote

> I would definitely have missed some of those.

It’s OK. I missed some of them the first time around, and there are some I’ve missed still that aren’t on that list because I’ve not found them.

I suspect that if we ever get to Phase 17 of this project (grin!) I’ll include a trap for Document.save and similar just to trap those events which haven’t been trapped by the macros.

For example, I have a cute little developer macro called VBEOpenUp that opens a template, adds one to the version number (Under278.dot –> Under279.dot) and resaves it. The code does not get trapped by FileSaveAs because it isn’t doing a SaveAs from the File menu – it is doing a doc.SaveAs in my VBA code.

In a similar manner we could have a user who crafts their own “save” macro, and we ought to trap that too. Perhaps the event handlers will come into their own on this project by trapping all those woul might unwittingly bypass our code.

Reply | Quote

Thanks for the clarification. As I suspected, your solution is much MUCH more complex than what I’m doing, so I think the DocumentBeforeSave event will be sufficient for my purposes. Still useful to have another option on standby, though. Thanks!

Reply | Quote

> Can I ask what kind of issues you’ve encountered using the Application events in Word?

At the risk of boring you to death, and since you are continuing with ap.events, I’ve been searching for a thread I read not two weeks ago. Kevin, I think it was, was bemoaning the fact that he couldn’t trap the big “X: top RH corner of the ap. window.

It’s not any of these: post 35,825, post 116,710 post 246,259 although other loungers may remember and locate the thread I’m thinking of. The problem was that of determining whether it was the application “X” or the document “X” that caused the document.close event to fire up, coupled with the issue that when the application “X” is clicked, it closes a single document.

Unless that was the sole document, in which case it quits the application.

Reply | Quote

Do you mean Function Override Not Functioning (Word 2002)?

Reply | Quote

Thank you Hans. That’s the one. That thread is better than my three references rolled together.
It was bugging the heck out of me because I’d read and studied it carefully late last month!

Reply | Quote

I have some code working for this, but also keep finding new questions. In Excel, I have created an add-in with a class module for my application event code, following examples in VBA’s help and other places on the internet (including here). However, in Word there is already an add-in using much the same method of accessing application events — a class module, with an object declared as Application, etc.

What is the best way of arranging my new code around the existing add-in? Can I create a completely separate add-in, with its own Application object and code, to run alongside the one that’s already in place? Or is it better to add my new code to the existing add-in, so there’s only one “hook” into the Word application rather than two concurrent ones?

I hope this makes some sort of sense; working with classes is very new for me, and though I’m fumbling my way through it, I can’t say that I truly understand what I’m doing yet.

Thanks!

Reply | Quote