" Save as" a workbook automatically on daily basis

[krjoshi]

Folks,
Is there any way (VBA) to save as a workbook automatically on daily basis in a particular folder? The file name should be with current date. Thanks in advance for your expertise.
*
Joshi

[smuzoen]

You can schedule Excel through using Windows Task schedular - you will need to be a little more specific - where is the workbook that is going to be saved and what is the workbook name/type - could you be a little more specific giving details of what workbook needs to be saved and how you want the filename to be e.g.filenameddmmyy.xlsx
This certainly can be done however if you could be a little more specific about the file to save and where it is to be saved to it would make things a lot easier. All you would need to do is place all the code inside macro enabled workbook in the workbook_open event and then schedule that workbook to be opened by Windows task scheduler at a certain time each day. If you could just explain in a little more detail then some code can be written for you.

[krjoshi]

Hi Smuzoen,

I have a workbook "Master JobList" saved in a share drive "M:\05-Shutdown and Turnarounds (ST)\05-12-AKG2\2013\02 - Joblist\04 - Updated Joblist", what I am looking for, the same file should save as automatically on daily basis to another folder "M:\05-Shutdown and Turnarounds (ST)\05-12-AKG2\2013\02 - Joblist\04 - Updated Joblist\backup". And, the file name like "Master JobList_11-Mar-12" based on the current date. Can you please help me?

Thanks in advance.

Joshi

[krjoshi]

Folks,

Please help me.

Thanks,
Joshi

[Chippy]

Try this VBScript file - paste the following code into Notepad and save as Backup.vbs. Test it by double-clicking Backup.vbs, wherever you saved it. If it works, create a scheduled task to run this script daily.

Dim FSO, FormattedDate

FormattedDate = Right("0" & Day(Date),2) & "-" & MonthName(Month(Date),True) & "-" & Right(Year(Date),2)

Set FSO = CreateObject("Scripting.FileSystemObject")

FSO.CopyFile "M:\05-Shutdown and Turnarounds (ST)\05-12-AKG2\2013\02 - Joblist\04 - Updated Joblist\Master JobList.xls", _
	"M:\05-Shutdown and Turnarounds (ST)\05-12-AKG2\2013\02 - Joblist\04 - Updated Joblist\backup\Master JobList_" & FormattedDate & ".xls"

Note - I've assumed your workbook has the file extension .xls. Change this in the code if I'm wrong.

[smuzoen]

Copy this code into a workbook - you can name it anything e.g. backupWorkbook.xlsm

Private Sub Workbook_Open()
Dim oldPath As String, newPath As String
oldPath = "M:\05-Shutdown and Turnarounds (ST)\05-12-AKG2\2013\02 - Joblist\04 - Updated Joblist" 'Folder file is located in
newPath = "M:\05-Shutdown and Turnarounds (ST)\05-12-AKG2\2013\02 - Joblist\04 - Updated Joblist\backup" 'Folder to copy file to
Set fs = CreateObject("Scripting.FileSystemObject")
Application.DisplayAlerts = False
fs.CopyFile oldPath & "\" & "Master JobList.xls", newPath & "\" & "Master JobList" & Format(Now, "ddMMMyy") & ".xls" 'This file was an .xls file
Set fs = Nothing
ActiveWorkbook.Close False
Application.DisplayAlerts = True
End Sub

In Task scheduler just add this workbook as a scheduled task to run at some time during the day

[krjoshi]

Hi Guys,

Thanks a lot. Both options are working great.

Thanks,
Joshi