×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Running Macros at a pre-set time

Running Macros at a pre-set time

Running Macros at a pre-set time

(OP)
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.

RE: Running Macros at a pre-set time

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.

RE: Running Macros at a pre-set time

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?

RE: Running Macros at a pre-set time

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"),_
   "D:excel\book1.xls!ThisWorkbook.test"

best regards

RE: Running Macros at a pre-set time

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.

RE: Running Macros at a pre-set time

(OP)
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)

RE: Running Macros at a pre-set time

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.

RE: Running Macros at a pre-set time

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

RE: Running Macros at a pre-set time

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"), "Test"
End sub


Sub Test()
Range("A1").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

RE: Running Macros at a pre-set time

(OP)
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

RE: Running Macros at a pre-set time

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources