[Solved] VBA: Can a workbook save & close itself ?

  • Evening !


    I have a workbook that opens another book ... refreshes it.. save and closes it....every 30 minutes.


    I need then to close the first book so that in 30 mins time the process can restart...at the moment the user has to close it manually.


    Any thoughts ??


    Colin

  • Can you explain a little more? Why does the "master" workbook have to be closed?


    If opening the master book is what causes the second book to be updated, then you might consider changing the workbook_open_event to a timer that updates the second file every half hour.


    If it does need to close, then you should be able to do that with something like:


    ActiveWorkbook.Close SaveChanges:=True


    as the last statement in the macro that closes/saves the second workbook.

  • Hi Colin -
    This will close excel with no questions asked (changes will be lost) - Take out the displayalerts line if you want to be prompted to save changes.


    Code
    Sub QuitExcel()
    Application.DisplayAlerts = False
    Excel.Application.Quit
    End Sub


    Hope this helps


    .....Ralph

  • Works well !


    If I have this as a scheduled task which opens up at the same time as another version of Excel is being used...is there code to just close down the version opened by the Scheduler ?


    Colin

  • Hi Colin,


    If you have more than one instance of Excel running, the code should only close all the workbooks running in the set where the macro is located. (Worked that way on mine).


    If you are running the Excel file from the Task Scheduler, make sure you are opening up a new version of Excel for the task and not just opening the excel file in the current Excel task (if excel is already running). In your "Run" box in the task scheduler, enter the full program name plus the excel file name - -


    "C:/Program Files/Microsoft Office/Office10/EXCEL.EXE" c:/test.xls


    (adjust for where your excel program file are, of course)


    .....Ralph

Participate now!

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