Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving to subdirectory using date()
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) -- pvm391 ----------------------------------------------------------------------- pvm3911's Profile: http://www.excelforum.com/member.php...fo&userid=2654 View this thread: http://www.excelforum.com/showthread.php?threadid=39846 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving to subdirectory using date()
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 @ ... pvm3911 Wrote: 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 +------------------------------------------------------------------- -- T-Že ----------------------------------------------------------------------- T-Žex's Profile: http://www.excelforum.com/member.php...fo&userid=2657 View this thread: http://www.excelforum.com/showthread.php?threadid=39846 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
saving a file with a date | Excel Worksheet Functions | |||
Saving with name and date macro | Excel Discussion (Misc queries) | |||
Date not Saving As Expected | Excel Programming | |||
Find Correct Subdirectory | Excel Programming | |||
Saving with date in filename | Excel Programming |