run code at specified time each day

[Djblois]

I want to create code to run each day at a specified time of day. The only think I can think of is using VBscript to start excel and then access VBA code through the VBscript. Is this possible? if it is can someone point me in the right direction. I have been looking online and in my multitude of books and can't find anything on using VBscript to run VBA code or ability to have VBscript or VBA code run at a specified time of day.

Thank you,
Daniel

[OTWarrior]

taking a guess here, but would setting up a scheduled event on the calendar to load the excel document (which has the code you wish to run on workbook open) work?

[figment]

you could try using Visual Studio (http://msdn2.microsoft.com/en-us/express/default.aspx) to wright a stand alown application that opens the needed spread sheets and performs the desired taskes. then you could use Windows "Scheduled Taskes" in the control panels to call your VB applications, at the desired time of day.

i am not sure about VB applications calling VBA macros, but it souldn't be to hard to copy the macros into the VB applications so that they are a part of it.

[Djblois]

the workbook open would partially work because then when someone wants to open it to look at it, it will run again. Well maybe I can setup a registry with the date it was updated last and if it wasn't updated today it will run but if it was it won't. Now, the other part sounds interesting, how do I set up a scheduled event to run on the calendar? also, how would I run this on a server?

[Bob Phillips]

'-------------------------------------------------------------
Private Sub Workbook_Open()
'-------------------------------------------------------------
Dim runDate

On Error Resume Next
runDate = Evaluate(ThisWorkbook.Names("_RunDate").RefersTo)
On Error GoTo 0
If runDate < Date Then
Call myJob
End If
ThisWorkbook.Names.Add Name:="_RunDate", RefersTo:=Date
ThisWorkbook.Names("_RunDate").Visible = False
End Sub


Control Panel>Scheduled Tasks>Add a Scheduled Task, and follow the wizard.

[Djblois]

Xld,

How do I get this to work with a server at my job? My job manually creates a lot of Reports each week that should be done automatically, which I want to figure out. I don't have access to Control Panel. Can you set up a scheduled task under someone's account on a server? or does it need to be done on the server itself?

Daniel

[Bob Phillips]

No it would need to be done on a server.

This job is for stopping it running again as you mentioned. If you can't schedule a job, then you could use OnTime if the workbook could be left open on the server.