Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically copy with save?
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? -- It is more disgraceful to distrust than to be deceived by our friends. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically copy with save?
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically copy with save?
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically copy with save?
GS wrote:
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. -- What in the Home Sweet Home is going on in there? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically copy with save?
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically copy with save?
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. 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 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 -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically copy with save?
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically copy with save?
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. 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. -- HAPPY NEW YEAR! stupid lag |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically copy with save?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
save automatically | Excel Discussion (Misc queries) | |||
How do I automatically save a backup copy of Excel 2007 spreadshee | Excel Discussion (Misc queries) | |||
Automatically Save | Excel Programming | |||
How to save a file without overwrite or save a copy? | Setting up and Configuration of Excel | |||
Automatically save a copy of worksheet from C: to Network Drive | Excel Discussion (Misc queries) |