Ontime vba opens another workbook that is not requested

[dode]

Hi

I have a workbook that I want to send an email from every hour. I have managed this but it randomly opens another workbook (not linked to this one and not always the same one).

Any ideas why this occurs?

13826

Thanks

George

[Paul_Hossler]

Well, the only link I see is to Plu excel.xlsm in g:\public\planning\hellinic


13828

[SamT]

First, you need to clean up your workbook, there are too many un-used subs and even modules, that it just isn't worth someone else's time to dig thru them all.

As a bonus, that cleanup might just fix your problem.

@ everybody:
I couldn't even close Excel normally with thatbook open. Don't enable Macros if you open it.

[dode]

Ok thanks. I did notice there were a lot of unused subs/ modules but I left them in if that might be the problem. I never had an issue with it closing though. This particular workbook changed many times over the years hence the surplus subs. I will tidy up.

Thanks for taking the time to reply.

[SamT]

Comment out some key line(s) in the sub, then test the book before deleting that sub.

Add the words "Option Private Module" at the top of the Module, then test the Workbook. IF it works, Export the module, only then delete the module and retest the book.

[dode]

Comment out some key line(s) in the sub, then test the book before deleting that sub.

Add the words "Option Private Module" at the top of the Module, then test the Workbook. IF it works, Export the module, only then delete the module and retest the book.

Thanks. I have deleted a lot of modules but will take time to test as it is pretty random when it happens. Thanks for the reply.

[dode]

13829

With modules/subs deleted.

I will run it all day and let you know what happens!

[SamT]

CU L8R

[snb]

It's always necessary to disable application.ontime instructions before closing a workbook.
All those separate modules for every day of the week are redundant.


See more: http://www.snb-vba.eu/VBA_Application.OnTime_en.html
(http://www.snb-vba.eu/VBA_Application.OnTime_en.html)

[dode]

It's always necessary to disable application.ontime instructions before closing a workbook.
All those separate modules for every day of the week are redundant.


Im not sure how separate modules are redundant as they are for different days which I deleted from the sheet before I uploaded. Could you explain please.

I will look at your link though.

Thanks for your help

[dode]

Im not sure how separate modules are redundant as they are for different days which I deleted from the sheet before I uploaded. Could you explain please.

I will look at your link though.

Thanks for your help

I have looked at your link but to be honest I dont understand how to make it work in my situation. I have tried something so I will see if that helps.

[dode]

Seems to have worked better today, so tidying up seems to have helped.






Thanks for your help

[dode]

Seems to have worked better today, so tidying up seems to have helped.


Started doing it today again. I am really at a loss.

Any ideas?



Private Sub Workbook_Open()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("run macro")
Application.OnTime Now + TimeValue("00:05:00"), "update_2"
Application.OnTime TimeValue(ws.Range("e3").Text), ws.Range("g3"), Text
Application.OnTime Now + TimeValue(ws.Range("h3").Text), ws.Range("g3"), Text
Application.OnTime TimeValue(ws.Range("f3").Text), "Closebook"
End Sub
Public Sub CancelOnTime()
Application.OnTime TimeValue(ws.Range("f3").Text), "update_2", , False
Application.OnTime TimeValue(ws.Range("f3").Text), ws.Range("g3"), Text, , False

End Sub


Thanks

George

[snb]

If you do nothing with the suggestions we make why would we offer any ?

[dode]

If you do nothing with the suggestions we make why would we offer any ?


What do you mean? I have tried everything I have been asked. I asked for help on the part to close ontime. I asked you to explain as I dont understand.

[SamT]

In the Workbook_BeforeClose sub, cancel all the OnTimes.

Example

This example runs my_Procedure 15 seconds from now.

Application.OnTime Now + TimeValue("00:00:15"), "my_Procedure"

This example runs my_Procedure at 5 P.M.

Application.OnTime TimeValue("17:00:00"), "my_Procedure"

This example cancels the OnTime setting from the previous example.

Application.OnTime EarliestTime:=TimeValue("17:00:00"), _
Procedure:="my_Procedure", Schedule:=False

[dode]

In the Workbook_BeforeClose sub, cancel all the OnTimes.

Thanks very much I will give it a go, much appreciated.

[dode]

In the Workbook_BeforeClose sub, cancel all the OnTimes.

So would I need an OnTime cancel for the first proceedure too.? Sorry, this is a bit above my level.

[SamT]

From the code you posted in Post #1`3, it looks like you need 4 Ontime Cancels in the BeforeClose sub.

[dode]

From the code you posted in Post #1`3, it looks like you need 4 Ontime Cancels in the BeforeClose sub.
Ok thanks. Trying that at the moment but having difficulty using the variables that I used to start them to then cancel them . I will let you know how I get on.

Thanks

George

[SamT]

Here is an example (http://www.vbaexpress.com/forum/showthread.php?53037-Colour-Entire-Row-Base-on-the-Value-of-Two-Other-Cells&p=327547&viewfull=1#post327547) of how i did it earlier. In your case, I would have the Workbook Open and BeforeClose subs call just one remote sub that initialized the time variables and started the four ontime subs.

[dode]

Here is an example (http://www.vbaexpress.com/forum/showthread.php?53037-Colour-Entire-Row-Base-on-the-Value-of-Two-Other-Cells&p=327547&viewfull=1#post327547) of how i did it earlier. In your case, I would have the Workbook Open and BeforeClose subs call just one remote sub that initialized the time variables and started the four ontime subs.

Thanks very much for all your help and guidance but its all way above my level.

Maybe have to press a button!


George

[SamT]

Private Sub Workbook_Open()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("run macro")
Application.OnTime Now + TimeValue("00:05:00"), "update_2"
Application.OnTime TimeValue(ws.Range("e3").Text), ws.Range("g3"), Text
Application.OnTime Now + TimeValue(ws.Range("h3").Text), ws.Range("g3"), Text
Application.OnTime TimeValue(ws.Range("f3").Text), "Closebook"
End Sub
Public Sub CancelOnTime()
Application.OnTime TimeValue(ws.Range("f3").Text), "update_2", , False
Application.OnTime TimeValue(ws.Range("f3").Text), ws.Range("g3"), Text, , False

End Sub

Show us subs, "run macro," "update_2," and "Closebook," along with sample values from ws.Ranges "e3", "f3", "g3", and "h3".

Actually, if you are allowed, use the "Go Advanced" option, and use the "Manage Attachments" button and upload the whole workbook.

[dode]

13860

Thanks for looking



Private Sub Workbook_Open()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("run macro")
Application.OnTime Now + TimeValue("00:05:00"), "update_2"
Application.OnTime TimeValue(ws.Range("e3").Text), ws.Range("g3"), Text
Application.OnTime Now + TimeValue(ws.Range("h3").Text), ws.Range("g3"), Text
Application.OnTime TimeValue(ws.Range("f3").Text), "Closebook"
End Sub
Public Sub CancelOnTime()
Application.OnTime TimeValue(ws.Range("f3").Text), "update_2", , False
Application.OnTime TimeValue(ws.Range("f3").Text), ws.Range("g3"), Text, , False

End Sub

Show us subs, "run macro," "update_2," and "Closebook," along with sample values from ws.Ranges "e3", "f3", "g3", and "h3".

Actually, if you are allowed, use the "Go Advanced" option, and use the "Manage Attachments" button and upload the whole workbook.

[SamT]

in re cleaning up the code; You have the indentical function, RangeToHTML, in the "ThisWorkbook" Module, every mail-(day) module and the "Module" Module. Rename the "Module: Module to"modRangeToHTML" and delete the function everywhere else.

I don't know what time in the morning that you open the workbook ... Using Mondays as an example, the code tries to run the sub mon at 6:20AM, (Start time)and every hour after that due to the ontime procedure in the mon sub, and at 7:30 AM, (Open) (and every hour after that for the same reason.)

If you happen to open the book at, say, 7:35 AM, what is going to happen is that the 7:30 sub is going to run and a few milliseconds later the 6:20 sub is going to try to run and this will reoccur every hour for the set of the day and they are really gonna mess up the sub's run. More about this Below...

Back to Cleaning up the code all the day sub are indentical except for the name of the sheet to work with.
If you named one MailReport and changed

Application.OnTime Now + TimeValue("01:00:00"), "FRI"
to
Application.OnTime Now + TimeValue("01:00:00"), "MailReport"
And changed

Set ws = ThisWorkbook.Worksheets("fri")
to
Set ws = ThisWorkbook.Worksheets(TodaysShortName)


then used this function in a module

Function TodaysShortname() As String
Select Case Weekday(Now)
Case 1: TodaysShortname = "SUN"
Case 2: TodaysShortname = "MON"
Case 3: TodaysShortname = "TUE
Case 4: TodaysShortname = "WED"
Case 5: TodaysShortname = "THU"
Case 6: TodaysShortname = "FRI"
Case 7" TodaysShortname = "SAT"
End Seelct
End Funtion
Then you could IMO delete all the other day modules. Ah... check the case is the same in all that.

I notice that the workbook is set to automatically close at 7:25 AM. Is it left open all night?

Tell us in your own words what the auto email schedule is supposed to be Monday thru Friday.

[dode]

in re cleaning up the code; You have the indentical function, RangeToHTML, in the "ThisWorkbook" Module, every mail-(day) module and the "Module" Module. Rename the "Module: Module to"modRangeToHTML" and delete the function everywhere else. That makes sense (to me now)


I don't know what time in the morning that you open the workbook ... Using Mondays as an example, the code tries to run the sub mon at 6:20AM, (Start time)and every hour after that due to the ontime procedure in the mon sub, and at 7:30 AM, (Open) (and every hour after that for the same reason.)

If you happen to open the book at, say, 7:35 AM, what is going to happen is that the 7:30 sub is going to run and a few milliseconds later the 6:20 sub is going to try to run and this will reoccur every hour for the set of the day and they are really gonna mess up the sub's run. More about this Below...

The 0620 time is a Production start time for that day and only used to add various Production times throughout the day to update the plan. This time should not used in the OnTime calculation


Back to Cleaning up the code all the day sub are indentical except for the name of the sheet to work with.
If you named one MailReport and changed

Application.OnTime Now + TimeValue("01:00:00"), "FRI"
to
Application.OnTime Now + TimeValue("01:00:00"), "MailReport"
And changed

Set ws = ThisWorkbook.Worksheets("fri")
to
Set ws = ThisWorkbook.Worksheets(TodaysShortName)


then used this function in a module

Function TodaysShortname() As String
Select Case Weekday(Now)
Case 1: TodaysShortname = "SUN"
Case 2: TodaysShortname = "MON"
Case 3: TodaysShortname = "TUE
Case 4: TodaysShortname = "WED"
Case 5: TodaysShortname = "THU"
Case 6: TodaysShortname = "FRI"
Case 7" TodaysShortname = "SAT"
End Seelct
End Funtion
Then you could IMO delete all the other day modules. Ah... check the case is the same in all that.


I notice that the workbook is set to automatically close at 7:25 AM. Is it left open all night? I wont have this open and they have a bad habit of leaving files open meaning those on the mailing list would get unnecessary emails. So I was trying to stop this from happening.


Tell us in your own words what the auto email schedule is supposed to be Monday thru Friday.

We work Sunday to Friday ( occasional Saturday) Single shift on Sunday and Friday and 2 shift Monday to Thursday. Sunday is a different start time (midday) and we work from Monday to Thursday 0620-0100 in the morning. The idea of the workbook is to email a hourly Production update. The areas of the worksheet to be copied are the same for each day. The only real difference for each day is the start and finish time. The finish time can vary slightly on any day (depending on workload). Thanks for taking the time to help me on this. I really do appreciate it. I have been trying for a while now to get it to work so unnecessary parts have been added.

I will follow your suggestions and see how I get on. Once again thanks.

George

[dode]

Seems to be working a lot better now and much tidier as well! The only part Im not 100% sure on is whether I still need the BeforeClose part also,or does the code in #23 cover this?

[SamT]

dode,

I worked on your book this weekend and did a lot of trying to make the Ontime routines work. By 1AM Monday, I had figured it all out, but Monday was already dedicated to my monthly shopping trip.

Check back periodically, but I should have your book working no later than 9 hours from this post's time..

[dode]

I cant thank you enough, thanks very much

George

[SamT]

This book is made to close a certain time after QuittingTime if there is no human activity in the time of MinutesInactivityBeforeCLose.

I Made my best guess as to the value in ConstantsAndVariables, but you need to check the out and adjust as needed.

When the book Opens, it runs StartTimers, which sets some of ConstantsAndVariables and a few others, then starts all the Timer subs to running.

When a User closes the book, the normal closing method is canceled and sub ForceClose takes over.

[dode]

Sorry but I am getting a couple of errors on open
Public Sub StartTimers()'Sets Booleans as needed and starts Ontimes With ConstantsAndVariables .SetTodaysShortname .SetTodaysQuittingTime End With EmailReportTimer 'Opening Run SendReportYesNo = True 'Set after the Opening run 'Start checking for activity at quitting time ConstantsAndVariables.WasActivity = True Application.OnTime EarliestTime:=ConstantsAndVariables.QuittingTime, _ Procedure:="CheckActivityTimer" End SubOn this line
With ConstantsAndVariablesOn trying to close
Public Sub CancelTimers() If Now > ConstantsAndVariables.QuittingTime Then _ Application.OnTime EarliestTime:=CheckActivityTime, _ Procedure:="CheckActivityTimer", Schedule:=False Application.OnTime EarliestTime:=TimeValue(SendReportTime), _ Procedure:="EmailReportTimer", Schedule:=False Application.OnTime EarliestTime:=ReCalculationTime, _ Procedure:="ReCalculateTimer", Schedule:=FalseEnd SubOn this line
Application.OnTime EarliestTime:=TimeValue(SendReportTime), _Obviously a lot of work there. Not totally sure on how to adjust all the variables, finish time specifically (I think I have sorted this). Apologies for the continuing requests.ThanksGeorge

[SamT]

Yeah, when I closed the book after that last post I did too. This version is pretty well tested. I did not test the send mail or the closing after quitting time, (the Check Activity Timer sub,), but the Range 2 HTML works.

One main issue with books with OnTime procedures is that you have to edit them with macros disabled and test them with macros enabled.

[dode]

Sorry getting an error here. When I try to close it the first time it does nothing and the second time it errors "Method OnTime of object_Application failed". It also sends the whole page for that day not the usual selected area.


Public Sub CancelTimers()










ConstantsAndVariables.WasActivity = False

If Now > ConstantsAndVariables.QuittingTime Then 'It's after quitting time
Application.OnTime EarliestTime:=CheckActivityTime, _
Procedure:="CheckActivityTimer", Schedule:=False
Else 'Quitting early, cancel starting check avtivity.
Application.OnTime EarliestTime:=ConstantsAndVariables.QuittingTime, _
Procedure:="CheckActivityTimer", Schedule:=False
End If

Application.OnTime EarliestTime:=TimeValue(SendReportTIme), _
Procedure:="EmailReportTimer", Schedule:=False

Application.OnTime EarliestTime:=ReCalculationTime, _
Procedure:="ReCalculateTimer", Schedule:=False




End Sub


On this line


Application.OnTime EarliestTime:=ConstantsAndVariables.QuittingTime, _
Procedure:="CheckActivityTimer", Schedule:=False

[SamT]

When I try to close it the first time it does nothing and the second time it errors "Method OnTime of object_Application failed".
If by "it does nothing" you mean that it doesn't close, then the error is normal and not a problem. The problem is that it did not close the first time.

If it is sending an email, that means that you edited the code.

Does my last attachment open and close without problems? It won't actually send any emails, it will just open a msgBox that says, "Running SendReport."

I did not edit RangeToHTML except to add the last line ; "ConstantsAndVariables.EmailSent = True"

You cannot edit the code in your book with Macros enabled.



Disable macros from untrusted sources then close Excel.
Open a copy of your book and delete ALL the code in it.
Open my Attachment and Drag and drop all the modules into your book.
Copy the code in my ThisWorkbook to your ThisWorkbook.
Save your book.
Enable macros.
Close Excel.
Open and test your book.


Do not edit the code in your book until you disable macros!

[dode]

If by "it does nothing" you mean that it doesn't close, then the error is normal and not a problem. The problem is that it did not close the first time.If it is sending an email, that means that you edited the code.Does my last attachment open and close without problems? It won't actually send any emails, it will just open a msgBox that says, "Running SendReport."I did not edit RangeToHTML except to add the last line ; "ConstantsAndVariables.EmailSent = True"You cannot edit the code in your book with Macros enabled.

Disable macros from untrusted sources then close Excel.
Open a copy of your book and delete ALL the code in it.
Open my Attachment and Drag and drop all the modules into your book.
Copy the code in my ThisWorkbook to your ThisWorkbook.
Save your book.
Enable macros.
Close Excel.
Open and test your book.

Do not edit the code in your book until you disable macros!When I test your workbook it does open and close without any problems. I have followed the above and when I try to close the workbook it send an email but the workbook doesnt close. When I try to close it again I get the error. Apologies.

[dode]

[Edit]Been leaving it to run today as seems to be working now apart the above so I dont know what will happen when it tries to close.

[SamT]

The error is because the first time you close, it cancels all the OnTimes, but the second time there are no OnTimes to cancel.

I suspect there might be a timing issue involving the creating and sending of the emails.

[dode]

Seems to be working ok now, so thanks very much for that. The only thing I would like to change is the QuittingTime on Sunday and Friday as they can vary. There is no user interaction on this sheet so the CheckActivity sub wont work in this instance.ThanksGeorge

[SamT]

The only thing I would like to change is the QuittingTime on Sunday and Friday as they can vary. There is no user interaction on this sheet so the CheckActivity sub wont work in this instance.

We can ask the User when the book opens at first shift what is quitting time on those days, but what if their best guess is short for any reason? The book will close at the quitting time they guess, whether or not they are still working.

[dode]

The maximum time they would work is 1 hour past the original start time. Is it possible to use a calculation to achieve this? Also it would be past midnight on most days. If it did close too early for some reason could they open it up and update it? Or is it possible to use a case select or a cell value?

[SamT]

The maximum time they would work is 1 hour past the original start time.
Hunh?!? What ?!?

[SamT]

SamT will be gone for a while (http://www.vbaexpress.com/forum/showthread.php?53176-SamT-will-be-gone-for-a-while)

[dode]

Hunh?!? What ?!?

Sorry, original finish time