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?

email upload.xls

Thanks

George

[Paul_Hossler]

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


Capture.jpg

[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]

email upload.xls

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

[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