AUTOMATICALY SAVE

D

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
 
B

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"
 
B

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"
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Please help macro for save 1
Simplify save code 11
MACRO SAVE HELP 2
Macro HELP PLEASE 4
Macro Time stamp Help 2
Save as marco 3
Intuitive Macro that changes based on cell value 13
Save with ref. to cell A1 2

Top