Consulting

Page 1 of 3 1 2 3 LastLast
Results 1 to 20 of 43

Thread: Ontime vba opens another workbook that is not requested

  1. #1
    VBAX Regular
    Joined
    Jun 2015
    Posts
    24
    Location

    Ontime vba opens another workbook that is not requested

    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

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,785
    Location
    Well, the only link I see is to Plu excel.xlsm in g:\public\planning\hellinic


    Capture.jpg
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,709
    Location
    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.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  4. #4
    VBAX Regular
    Joined
    Jun 2015
    Posts
    24
    Location
    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.
    Last edited by dode; 06-30-2015 at 10:37 PM.

  5. #5
    VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,709
    Location
    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.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  6. #6
    VBAX Regular
    Joined
    Jun 2015
    Posts
    24
    Location
    Quote Originally Posted by SamT View Post
    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.

  7. #7
    VBAX Regular
    Joined
    Jun 2015
    Posts
    24
    Location
    email upload.xls

    With modules/subs deleted.

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

  8. #8
    VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,709
    Location
    CU L8R
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  9. #9
    snb
    Guest
    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

  10. #10
    VBAX Regular
    Joined
    Jun 2015
    Posts
    24
    Location
    Quote Originally Posted by snb View Post
    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

  11. #11
    VBAX Regular
    Joined
    Jun 2015
    Posts
    24
    Location
    Quote Originally Posted by dode View Post
    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.
    Last edited by dode; 07-01-2015 at 02:00 PM.

  12. #12
    VBAX Regular
    Joined
    Jun 2015
    Posts
    24
    Location
    Seems to have worked better today, so tidying up seems to have helped.






    Thanks for your help

  13. #13
    VBAX Regular
    Joined
    Jun 2015
    Posts
    24
    Location
    Quote Originally Posted by dode View Post
    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
    Last edited by SamT; 07-02-2015 at 10:26 AM. Reason: changed HTML Code Tags to VBA Code tags.

  14. #14
    snb
    Guest
    If you do nothing with the suggestions we make why would we offer any ?

  15. #15
    VBAX Regular
    Joined
    Jun 2015
    Posts
    24
    Location
    Quote Originally Posted by snb View Post
    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.

  16. #16
    VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,709
    Location
    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
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  17. #17
    VBAX Regular
    Joined
    Jun 2015
    Posts
    24
    Location
    Quote Originally Posted by SamT View Post
    In the Workbook_BeforeClose sub, cancel all the OnTimes.
    Thanks very much I will give it a go, much appreciated.

  18. #18
    VBAX Regular
    Joined
    Jun 2015
    Posts
    24
    Location
    Quote Originally Posted by SamT View Post
    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.

  19. #19
    VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,709
    Location
    From the code you posted in Post #1`3, it looks like you need 4 Ontime Cancels in the BeforeClose sub.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  20. #20
    VBAX Regular
    Joined
    Jun 2015
    Posts
    24
    Location
    Quote Originally Posted by SamT View Post
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •