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