Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
AUTOMATICALY SAVE
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
AUTOMATICALY SAVE
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
AUTOMATICALY SAVE
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can you make excell automaticaly save to the current date & time? | Excel Discussion (Misc queries) | |||
Can you automaticaly save a shared file every few mintues? | Excel Discussion (Misc queries) | |||
macro to save file automaticaly? | Excel Discussion (Misc queries) | |||
Macro to automaticaly save a book under an specific name | Excel Programming | |||
refresh a cell automaticaly each second ? | Excel Programming |