Run Code Weekly

[Jaye7]

Can someone please provide a script that I can use so that my macro will run once a week, my macro is named "Copy_Files2"

Obviously if I didn't open the workbook for 10 days it would need to run the code automatically as 7 days had passed.

It is used so that each week it copies some files and deletes others, I have found scripts that will run a process daily but not weekly.

Thanks.

 

[Michael M]

Hi
try
This will run the macro at 2:30pm on Friday. I think the workbook will have to be open at the time though...and others may be able to improve on that
You could look here for using a closed workbook

http://www.ozgrid.com/forum/showthread.php?t=78499

Sub FriRun() 
    If Weekday(Now()) = 6 Then 
        Application.OnTime TimeValue("14:30:00"), "Copy_Files2" 
    End If 
     
End Sub 
 
Sub Copy_Files2() 
     
     ' your code goes here
     
End Sub

 

[rattman]

I am at work so a long reply is not possible. I use the CONTROL PANEL/SCHEDULED TASK to run a spreadsheet at a specific time and day. Just browse to the Excel sheet and pick it as a task. Then in the spreadsheet use the macro auto_open and put the code you need before the sheet is saved.

Using this method the sheet is not even open. I also use the ONHOUR command but that requires the sheet to be open. It works well when a sheet is gathering information and then you want to clean the data and pass it on to a different database.
Hope that helps,
If not I can be more descriptive.
rattman

 

[Ruddles]

Another vote for Rattman's suggestion. I use this method and it works perfectly because you're not relying on Excel being active and you don't need to write any code to handle the scheduling, missed events, etc.

 

[Jaye7]

Thanks for your help guys, I haven't ever used the control panel method before and now that I know about it it has some great possibilities for the future.

 

[Michael M]

Yeah, nice call Rattman.
I haven't used it before, so I'll have to give it a whirl.