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"
-------------
Peter Richardson
"DP7" wrote:
> 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
|
Similar Threads
