Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
tomorrow's date
A big thank you to J. E. and Myrna!! this is absolutely brilliant
J. E. Thanks for shortening the code. If I output as a batch file how come I cannot start my batch file with program = shell("D:\users\default\renproc.bat", 1) Once again Thanks a million Public Sub MakeMy_Code() Call mk_symb(Worksheets("Sheet1")) With Worksheets("Sheet1") .Range("A1").Value = "@echo off" .Range("A2").Value = "cls" .Range("A3").Value = "ren q:\Backupddmm.zip backup" & _ Format(Date + 1, "ddmm") & ".zip" End With Call WriteTXT 'tells Excel the workbook is saved then quits Excel 'program = Shell("D:\users\default\renproc.bat", 1) ActiveWorkbook.Saved = True Application.Quit End Sub Sub WriteTXT() ' below used to bypass Excel's prompt to save Application.DisplayAlerts = False ChDir "D:\users\default" ActiveWorkbook.SaveAs Filename:="D:\users\default\renproc.bat", FileFormat _ :=xlText, CreateBackup:=False 'a timed delay.. to disable place a (') before Application Application.Wait Now + TimeSerial(0, 0, 1) End Sub "J.E. McGimpsey" wrote in message ... You could tweak your code a bit to be more efficient: Public Sub MakeMy_Code() Call mk_symb(Worksheets("Sheet1")) Call mk_symb(Worksheets("Sheet2")) With Worksheets("Sheet2") .Range("A1").Value = Format(Date + 1, "dd") .Range("A2").Value = Format(Date + 1, "mm") End With With Worksheets("Sheet1") .Range("A1").Value = "@echo off" .Range("A2").Value = "cls" .Range("A3").Value = "ren q:\Backupddmm.zip backup" & _ Worksheets("Sheet2").Range("A1") & _ Worksheets("Sheet2").Range("A2") & ".zip" End With Call Save 'tells Excel the workbook is saved then quits Excel ActiveWorkbook.Saved = True Application.Quit End Sub Public Sub mk_symb(wkSht As Worksheet) With wkSht .Range("D7").Clear .Columns("A:A").NumberFormat = "@" End With End Sub and if you really don't need to save the day and month in sheet2, you could shorten it to: Public Sub MakeMy_Code() Call mk_symb(Worksheets("Sheet1")) With Worksheets("Sheet1") .Range("A1").Value = "@echo off" .Range("A2").Value = "cls" .Range("A3").Value = "ren q:\Backupddmm.zip backup" & _ Format(Date + 1, "ddmm") & ".zip" End With Call Save 'tells Excel the workbook is saved then quits Excel ActiveWorkbook.Saved = True Application.Quit End Sub In article , "stat" wrote: I use the following code to write code to create a text file that eventually becomes a batch file to run an automated process. how can I specify tomorrow's date instead of today? thanks again Sub MakeMy_Code() Dim Command Sheets("Sheet1").Select Call mk_symb Sheets("Sheet2").Select Call mk_symb If Day(Now()) < 10 Then Range("A1") = "0" & Day(Now()) Else Range("A1") = Day(Now()) End If If Month(Now()) < 10 Then Range("A2") = "0" & Month(Now()) Else Range("A2") = Month(Now()) End If Sheets("Sheet1").Select Range("A1") = "@echo off" Range("a2") = "cls" Range("a3") = "ren q:\Backupddmm.zip backup" & Range("'Sheet2'!A1") & Range("'Sheet2'!a2") & ".zip" Call Save 'tells Excel the workbook is saved then quits Excel ActiveWorkbook.Saved = True Application.Quit End Sub Sub mk_symb() Cells.Select Range("D7").Clear Range("B6").Select Columns("A:A").NumberFormat = "@" Range("A1").Select End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to return tomorrow's date. | New Users to Excel | |||
tomorrow's date on an invoice made today | New Users to Excel | |||
Report Date - Date Recv = Days Late, but how to rid completed date | Excel Worksheet Functions | |||
Date updates from worksheet to chart & changes date to a date series! Help!! | Charts and Charting in Excel | |||
tomorrow's date | Excel Programming |