VBA: Sheet Runs OK ..but NOT under Scheduler

  • One Sheet runs, save and closes another ...then closes itsef and excell down perfectly when run OUTSIDE the windows scheduler.... but when fired by the scheduler it does everything perfectly ... except it will not close down Excel ... the Scheduler says the schedule is " Running" The path used is the full F:Users/etc etc


    Any bright ideas ??

  • Hi Colin,


    I assume this is a followup to last weeks thread http://www.ozgrid.com/forum/viewthread.php?tid=6802


    I tried similar code running from the Task Scheduler and everything shut down as it was supposed to. I did notice 1 thing - in the Task Scheduler window, the "Status" column would show "running" for several seconds after the Excel Window closed down. Sometimes I even had to click on View / Refresh before the "running" would change back to a blank. Could that be the problem - or is Excel really still open in its own window?


    .....Ralph

  • Ralph,


    No ... the window stays open when using the scheduler


    The code used is .....


    Private Sub Workbook_Open()
    Application.ScreenUpdating = False


    Workbooks.Open Filename:="F:\Users\COLINN\cooked\INTAKEStock.xls"
    Application.Run "INTAKEStock.xls!RefreshQuery"
    ActiveWorkbook.Save
    ActiveWindow.Close

    ActiveWorkbook.Close SaveChanges:=True


    Application.DisplayAlerts = False
    Excel.Application.Quit


    Application.ScreenUpdating = True



    End Sub


    I have tried scheduling it for once only , Daily etc .. all the same !


    Colin

  • Colin,


    I don't remember if you had other workbooks open or not.... the way the code you posted is written,


    ActiveWorkbook.Save
    ActiveWorkbook.Close


    will close INTAKEstock.xls - -


    then the next line
    ActiveWorkbook.Close SaveChanges:=True
    will close the current workbook (the one that contains the Workbook_Open macro - -
    so it never gets to the Excel.Application.Quit line.


    Change that line to ActiveWorkbook.Save if you have to save that workbook too, then try shutting down the app.


    BTW, the ScreenUpdating=True line is not needed - since it is after the Application.Quit, it will never be executed either.


    .....Ralph

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!