Automatically copy with save?

[Auric__]

So, my main biz workbook, I want to automatically copy the saved version
every time I hit save. Here's what I'm using right now:

Private Const MAX_COMPUTERNAME_LENGTH As Long = 31

Private Declare Function GetComputerName Lib "kernel32" _
Alias "GetComputerNameA" (ByVal lpBuffer As String, _
nSize As Long) As Long
Private Declare Function CopyFile Lib "kernel32" Alias "CopyFileA" ( _
ByVal lpExistingFileName As String, _
ByVal lpNewFileName As String, _
ByVal bFailIfExists As Long) As Long

Private saveflag As Boolean

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
If saveflag Then
saveflag = False
Else
Dim maxlen As Long, cname As String
Dim to_E As Boolean, to_F As Boolean
maxlen = MAX_COMPUTERNAME_LENGTH + 1
cname = Space$(maxlen)
GetComputerName cname, maxlen
cname = Left(cname, maxlen)
If UCase$(cname) = "LILITH" Then
If Not SaveAsUI Then
saveflag = True
Me.Save
saveflag = False
Cancel = True
Select Case Left$(Me.Path, 1)
Case "E"
to_F = True
Case "F"
to_E = True
Case Else
to_E = True
to_F = True
End Select
If to_E Then CopyFile Me.FullName, _
"E:\Business Records\" & Me.Name, 0
If to_F Then CopyFile Me.FullName, _
"F:\Business Records\" & Me.Name, 0
End If
End If
End If
End Sub

In short:
- if my flag hasn't been set:
- if I'm working from my tablet ("Lilith"):
- set my flag
- do the actual save
- clear my flag
- cancel the pending save that got me here in the first place
- check which drive the book was opened from
- copy as appropriate

I would think that there must be a simpler way to do this, without having
to manually save (so to speak). The problem is, if I just throw a copy in
BeforeSave without going through these gyrations, the current version isn't
what gets copied over, since this runs *before* the save.

Any suggestions?

 

[GS]

It's not clear what you're trying to do! It seems that you have a
roaming file that may be used on various machines, but that you want to
save it to a particular machine regardless of where it's run from.

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

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.

--
Garry

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

 

[GS]

Additionally, you might want to do 'Save' to update the file in place,
followed by 'SaveAs' to update the file in the target folder on the
other drive. (Assumes each machine knows which its 'Save' drive is, and
where to 'SaveAs', based on ComputerName perhaps?)

--
Garry

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

 

[Auric__]

It's not clear what you're trying to do! It seems that you have a
roaming file that may be used on various machines, but that you want to
save it to a particular machine regardless of where it's run from.

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.

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.

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.

 

[GS]

You do know you can save within the BeforeSave event, right? This would
require temporarily disabling events. Also, you can manage this in the
BeforeClose event!

--
Garry

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

 

[GS]

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.

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

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

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

--
Garry

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

 

[GS]

If you use the With ThisWorkbook construct as suggest in my *air code*
you can eliminated the added ref in "& ThisWorkbook.Name", to read "&
..Name"!

--
Garry

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

 

[Auric__]

Auric__ has brought this to us :


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

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

Yeah, I can see that. ;-)

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

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.

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.

 

[GS]

Auric__ explained :

Auric__ has brought this to us :


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

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

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