Loop a function

[fcomino]

Hi again

Is it possible to loop this code so that it does it every day? Currently it runs the commands at 7:00, 7:02, 17:00 and 17:02 then stops. The only way I can get it going again is if I close the workbook and re open it.


Private Sub Workbook_Open()

Application.OnTime TimeValue("07:00:00"), "CountMail"
Application.OnTime TimeValue("07:02:00"), "SaveAsTodaysDate"
Application.OnTime TimeValue("17:00:00"), "CountMail"
Application.OnTime TimeValue("17:02:00"), "SaveAsTodaysDate"

End Sub

Thanks

[ajaykgarg]

Hi

Currently, do you open the file manually before it runs at 7:00, 7:02, 17:00 and 17:02, or does it run even if the file is closed?

I think one solution could be setting it up as a scheduled task of windows.

Thanks
Ajay

[fcomino]

I have to have the Excel File open for it to work.

It would be great if it could run closed, however I am not too fussed for the minute. I just want it to loop so that it runs at those times everyday.

What is the best and easiest way to do this?

[foxguy]

Haven't tried this, but I can't see any reason it wouldn't work.

Please Login or Register  to view this content.

[fcomino]

Unfortunately this will not work, I tried it, and it does not recognise the function. I am guessing this is because I am trying to run the same function within itself.

How can I just run a loop?

[foxguy]

Of course, Workbook_Open() is a private Sub

Please Login or Register  to view this content.

[fcomino]

I am getting the error message:

The macro "C:\<FileName>.xls'!Run_Subs' cannot be found.

Also in Line 2 of your text, I presume you have a typo Rub=Run

[foxguy]

You probably have to put Run_Subs() in a standard module. Don't keep it in ThisWorkbook module.
Correct, just a typo.

[fcomino]

Thanks for your help - however it was getting too messy.

I have used the Task Scheduling option so that it opens the excel file 1 minute before. (6:59AM).

I have also created a new module called "Quit". Which closes the Excel File.

Sub Quit()

Application.Quit

End Sub



So this is what my formula now looks like:

Private Sub Workbook_Open()

Application.OnTime TimeValue("07:00:00"), "CountMail"
Application.OnTime TimeValue("07:02:00"), "SaveAsTodaysDate"
Application.OnTime TimeValue("17:02:30"), "Quit"
Application.OnTime TimeValue("17:00:00"), "CountMail"
Application.OnTime TimeValue("17:02:00"), "SaveAsTodaysDate"
Application.OnTime TimeValue("17:02:30"), "Quit"


End Sub

[ajaykgarg]

You may want to open the excel file 2-3 minutes before, to be sure it is open before your first procedure fires. (Just a precaution for system running slow on a given day)

[foxguy]

fcomino;

Boy, if you think that is getting messy, you are living a charmed life . I wish I had just one file that was only 10 times as messy as what you have. But if it works for you, that's what's important.

[foxguy]

fcomino;

I'll keep this thread in my subscriptions list for at least a month, in case you write more. After that you'll have to send me a private message to let me know you're back