PDA

View Full Version : VBA code runs when workbook is closed?



thomas.szwed
12-12-2007, 04:32 AM
I am looking to see if there is a way to run a marco/sub at a 11pm in the evening. I have created a new workbook with the following code.....what i want to do is for it to make a copy of anohter workbook - saving it in a pre determined folder. But i dont want any user interaction. Can the code do all of this itself?



Sub Backup()
Fname = NewStarterTracker.Name
Workbook("TestGood5").SaveCopyAs "\\ukyorw09\HR\IAC (file://\\ukyorw09\HR\IAC) New Starter Tracking\IAC\Application\Backup\ & Fname"
ActiveWorkbook.Save
ActiveWorkbook.Close

End Sub



I need to know how to nominate which workbook to backup. My current code Workbook(Itsname).SaveCopyAs doesnt work....do i need to put its path in somehow? Can ne1 help?

figment
12-12-2007, 06:39 AM
Workbook("TestGood5").SaveCopyAs "\\ukyorw09\HR\IAC New Starter Tracking\IAC\Application\Backup\" & Fname

thomas.szwed
12-12-2007, 06:41 AM
Isnt that what i aleady have, whats the difference?

mikerickson
12-12-2007, 06:45 AM
If Excel is running at 11PM and OnTime has been used to set the macro "BackUp" to run at 11PM, the workbook containing "BackUp" will be opened and BackUp run automaticaly at 11PM.

thomas.szwed
12-12-2007, 06:47 AM
Right, excel wont be open you see. Therefore what happens if i used Windows task scheduler to open it? But each users machine will be turned off at night?

figment
12-12-2007, 06:48 AM
i moved the " so that you have a string + the file name befor you have one string

your code always tryed to save to "\\ukyorw09\HR\IAC New Starter Tracking\IAC\Application\Backup\ & Fname" which probibly failed because you have a & in the file name

mine saves to "\\ukyorw09\HR\IAC New Starter Tracking\IAC\Application\Backup\" plus Fname

now it still might fail, for i am not sure what NewStarterTracker.Name generates as a name

thomas.szwed
12-12-2007, 06:51 AM
It orginally said "ThisWorkbook.Name" So i assumed i could replace this and call the backup what i want?