Auric__ explained :
GS wrote:

Auric__ has brought this to us :

Let me clarify, then. My main biz workbook is in several places: a copy
is on my workstation (and irrelevant to this problem), a copy is on my
server (also irrelevant), and the main working location of the workbook
is on my tablet on an SD card (E:) with a copy on a USB drive (F:).

Before I wrote the code I posted previously, I was manually copying the
workbook from E: to F:. (Sometimes I manage to open the workbook from
F: instead of E:, therefore the part of my code that checks which drive
the book lives in. It also takes into consideration the fact that I
might end up with a copy on, say, the hard drive in the future.)

What my code does is automate the copying. Part of my problem is that
the only place I know of to put the code to do so is in
Workbook_BeforeSave, which of course runs *before* the actual save.
What I want is to do the copying *after* the save, so that the same
version of the workbook is on both E: and F:. To do so requires the
gyrations I mentioned in my previous post.

You can do this in the BeforeClose event so all you have to do when
you're finished working in the file is click the close button.
Alternatively, you can us a sub (located in a standard module) named
"Auto_Close". This is my preference as there's various reasons why code
in the ThisWorkbook component may not always work as expected. I doubt
this to be possible with trivial projects, but since most of my stuff
is fairly complex I can't take any chances that shutdown code might not
execute properly.

Now that you mention it, Auto_Close sounds familiar. Unfortunately, I don't
see an equivalent "Auto_Save", which would be *perfect* for my needs. My
main concern really is copying the file *every* time I save, not just when
I close. (This damn tablet locks up hard about once a week, and twice I've
had to do a warranty repair... and I don't have anything else that can read
SD cards.)

ThisWorkbook.Path is the location of the file running the code.

I use Me instead of ThisWorkbook simply because it's less typing. In
context, they're identical.

As mentioned, I use an Auto_Close sub and so the "Me" keyword doesn't
work there!<g

Yeah, I can see that. ;-)

You could open a folder browser dialog so you can select the path to
save to. This obviates having to hard code path/drive letters and
folders, and would simplify coding as well as give you some
flexibility.

Hardcoding the paths is not a concern in this specific instance. I'm
the only user of this tablet, and I decide what letters are assigned to
what drives.


As for using Save vs SaveAs, I considered using SaveCopyAs, but since I
rely on timestamps for various purposes (which are unimportant here), I
*really* want them identical.

It sounds pretty straight forward, then, that you simply need save one
and copy it to the other drive[s]...

Dim sPath$, sCopyPath
sPath = ThisWorkbook.Path
If Right(sPath, 1) < "\" Then sPath = sPath & "\"

With ThisWorkbook
.Save
Select Case Left(sPath, 1)
Case "E"
sCopyPath = Replace(sPath, "E", "F")

Case "F"
sCopyPath = Replace(sPath, "F", "E")
End Select 'Case Left(sPath, 1)
.SaveCopyAs sCopyPath & ThisWorkbook.Name

..where you can insert as needed if you want to include more locations.
In the case of multiple drives, replace the Select Case with something
like...

Const sSaveToDrives$ = "C,D,E,F" '//edit to suit
Dim vDrv, sDrv$

sDrv = Left(sPath, 1)
For Each vDrv in sSaveToDrives
If vDrv < sDrv Then _
.Save¤CopyAs Replace(sPath, sDrv, vDrv) & ThisWorkbook.Name
Next 'vDrv

The only problem is, I want this to run when I save in the usual manner,
i.e. ctrl+s or the "save" icon or the "save" menu item. I don't want to
have to create my own custom save button (or whatever) just to get this
done.

I suppose what I really need is some way to hook the save action *after*
the save is complete. Maybe some way to moniter the Workbook.Saved
property, perhaps.

I think, then, the BeforeSave event is looking to be the best place to
get this done. You could have it call a separate routine OR, use this
event to 'hook' the normal save action so your code runs in its place.

I read where you said you don't want a custom save routine but IMO it's
probably th better way to go!

Also, I just now tried SaveCopyAs, but the files aren't identical.
(Different sizes for the saved files, even though the *contents* are
identical when extracted out via 7-zip.) Weird, and unacceptable.

I'm starting to think that maybe the best solution is to go outside of
Excel and schedule a file copy every few minutes via the scheduler. Hell,
maybe I can have the scheduler automatically save the workbook first,
too... I'll need to look into that.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion