AUTOMATICALY SAVE

[DP7]

I think a macro is the best way to do this; if there is a way that is better
I am open to all suggestions. I am trying to create a backup copy of one of
my spreadsheets on a daily basis. I need this to be done every weekday night
@ around 11PM when I know all updates for that day should be done. I need the
file name to contain the date from that day. So far I have the macro below as
my starting point. I have tried doing different things to get what I want; I
have had varying degrees of success. If anyone has any ideas as to how I
should attack this I would be very grateful.
Thanks in advance.




Sub Saveas()
'
' Saveas Macro
' Macro recorded 10/11/2007 by
'

'
ChDir _
"R:\Production Reports\Production Summary Report\Daily Production
Summary Copies"
ActiveWorkbook.Saveas Filename:= _
"R:\Production Reports\Production Summary Report\Daily Production
Summary Copies\Book1.xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

 

[barnabel]

Do you have the file open all the time? You would have to have the file open
and set up a timer event to fire off the macro to save the file.

If the file is not kept open, I would use a short VBS script and schedule it
to run using schedule tasks from the control panel (assuming you are running
windows NT or later)

The script would look something like:
---------------
dim filesys
dim today


today = datepart("YYYY",date)*10000+datepart("M",date)*100+datepart("D",date)
set filesys=createobject("Scripting.FileSystemObject")

filesys.CopyFile "R:\Production Reports\Production Summary Report\Daily
Production Summary Copies\book1."+cstr(today)+".xls", "{pathToFile}\book1.xls"

 

[barnabel]

Do you have the file open all the time? You would have to have the file open
and set up a timer event to fire off the macro to save the file.

If the file is not kept open, I would use a short VBS script and schedule it
to run using schedule tasks from the control panel (assuming you are running
windows NT or later)

The script would look something like:
---------------
dim filesys
dim today


today = datepart("YYYY",date)*10000+datepart("M",date)*100+datepart("D",date)
set filesys=createobject("Scripting.FileSystemObject")

filesys.CopyFile "R:\Production Reports\Production Summary Report\Daily
Production Summary Copies\book1."+cstr(today)+".xls", "{pathToFile}\book1.xls"