Running Macros at a pre-set time 1

[Chamberlin]

Hi Can anyone help. I want to run a macro at 6.00am every morning, is there a way of doing this?

I also want the macro to copy data to a networked workbook which will be read only, but it only works if the worksheet is open on my PC and then I have to save it and close for it to be available on the network, what am I doing wrong?

Any help would be greatly appreciated, please use laymens terms, I'm only a simple engineer!

Regards

Darren.

 

[dsi]

You would probably have to trigger a program (exe) from the server side. You can write a VB program to copy the data as needed. Outside of that, I am not sure how you would initiate the process at a certain time. Writing a program like this is really simple, you just need to be able to fire it up at the desired time.

DimensionalSolutions@Core.com
While I welcome e-mail messages, please post all thread activity in these forums for the benefit of all members.

 

[melone]

Could you place your macro in the windows starup directory and set your bios to power on your computer every morning at 6:00 am?

 

[mkoijn]

You need to use the OnTime method
For example the following code executes the macro test in the ThisWorkbook code module of the book1 workbook at 6:00am

Application.OnTime TimeValue("6:00 AM&quot,_
"D:excel\book1.xls!ThisWorkbook.test"

best regards

 

[dsi]

Yeah, but wouldn't you have to have the file/program running constantly to consistantly monitor the time?

DimensionalSolutions@Core.com
While I welcome e-mail messages, please post all thread activity in these forums for the benefit of all members.

 

[Chamberlin]

Thanks Everybody for the help.

The workbook will be open all of the time anyway, I'm interfacing with a PLC draging the data from I/O, data reg's and counters at 1 min intervals so that's not a problem.

Finnigan, does the string go above the macro in the VB editer? if not where do I insert the initiation string

Have I got to allocate a cell to the 'now' time function?

Thanks again for your help

Darren. (Chamberlin)

 

[tomatge]

It's probably not the answer to this particular question, but I'd like to pick up on the point from Melone above. Windows 98/2000/XP have a "Task Scheduler" which can allow you to automatically run tasks or programs at predetermined times or intervals. This would be a whole load easier than having to power up the computer at the right time.

 

[Chamberlin]

tomatge, I'm running windows NT. I'm not sure if it has a task scheduler, I can't find it anywhere.

 

[mkoijn]

Chamberlin, the ontime code is a seperate macro which could be in the same module as the one which you want to run at 6:00AM, as shown below.


Sub ontime()
Application.OnTime TimeValue("6:00:00&quot, "Test"
End sub


Sub Test()
Range("A1&quot.Value = 10
End Sub


I presume you want to run the macro at 6:00AM automatically because no one is there to initiate it manually, if this is the case you would have to set the "ontime" macro running say, the night before and then at 6:00AM the number 10 would be inserted into the Active workbook for example.

Best regards

 

[Chamberlin]

Thanks Finnigan, works like a dream.

Is there a way of putting the macro in a loop so I don't have to start it every day, so it will run a 6am every morning regardless of starting it the day before?

If not its not a big deal just getting this far will save a lot of work.

thanks again for all of your help

Darren

 

[joerd]

Yes, just add the Application.Ontime statement at the end of the macro you want to run:

Sub Test()
    Range("A1").Value = 10
    Application.OnTime TimeValue("6:00:00"), "Test"
End Sub

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.