Saving to subdirectory using date()

[pvm3911]

I have a night audit report named DSR(v1.20) in a directory name
Naudit. There's a subdirectory named Bak. I've tried without success t
create a macro that will save the DSR to Bak as a two-digit numbe
consisting of yesterday's date and overwrite any previous file of th
same name. E.g. Around 04:00 on the 22nd, when the DSR for the 21st i
complete, I'd like the operator to hit Ctrl+B, and the DSR will b
saved in Bak as '21' (taking today's date minus 1). Thus, Bak wil
contain DSRs for '01' thru '31', and on the 1st of the next month (er
actually, at 04:00 on the 2nd) the old '01' will be overwritten by th
new one.

Can this be done with a macro, or does it call for a VBA program, or
DOS batchfile? Can anyone suggest a pre-existing routine that I ca
modify to achieve my desired result? (Possible problem: at 04:00 on th
1st, the filename will vary; it can be either '30' or '31' [or '28' o
'29'], so instead of using date() it may be necessary to get input fro
the operator)

 

[T-®ex]

hi!
this code may be able to help you...

Sub CreateBackup(TimeStamp As Date)
Dim OrigFileName As String
Dim BackupName As String
Dim BackupDir As String
Dim TheHour As Integer
Dim TheDay As Integer

If TimeStamp <= -1 Then
TimeStamp = Now
End If

TheHour = Hour(TimeStamp)

If TheHour >= 4 Then
TimeStamp = TimeStamp - 1
Else
TimeStamp = TimeStamp - 2
End If

TheDay = Day(TimeStamp)

OrigFileName = ActiveWorkbook.FullName

'This folder (Backup Files) must already exist in the sam
directory as this workbook resides.
BackupDir = ActiveWorkbook.Path & "\Backup Files\"

BackupName = BackupDir & "DSR" & Format$(TheDay, "00") & ".xls"

Application.DisplayAlerts = False

ActiveWorkbook.SaveAs BackupName
ActiveWorkbook.SaveAs OrigFileName '"revert" to original file

Application.DisplayAlerts = True
End Sub

you kept mentioning the time 04:00 (am/pm?)... is this your cut-of
time.
does it mean that when the user hits Ctrl+B before 4, the backup dat
should be 2 days behind?

i've attached a zip file containing an excel file (with vba code) as
demo...
feel free to examine (and modify/use) it...

Hope I was able to help...
if you have questions, you can email me @ (e-mail address removed)...

I have a night audit report named DSR(v1.20) in a directory name
Naudit. There's a subdirectory named Bak. I've tried without success t
create a macro that will save the DSR to Bak as a two-digit numbe
consisting of yesterday's date and overwrite any previous file of th
same name. E.g. Around 04:00 on the 22nd, when the DSR for the 21st i
complete, I'd like the operator to hit Ctrl+B, and the DSR will b
saved in Bak as '21' (taking today's date minus 1). Thus, Bak wil
contain DSRs for '01' thru '31', and on the 1st of the next month (er
actually, at 04:00 on the 2nd) the old '01' will be overwritten by th
new one.

Can this be done with a macro, or does it call for a VBA program, or
DOS batchfile? Can anyone suggest a pre-existing routine that I ca
modify to achieve my desired result? (Possible problem: at 04:00 on th
1st, the filename will vary; it can be either '30' or '31' [or '28' o
'29'], so instead of using date() it may be necessary to get input fro
the operator)

+-------------------------------------------------------------------
|Filename: BackupTest.zip
|Download: http://www.excelforum.com/attachment.php?postid=3728
+-------------------------------------------------------------------