I have a macro that opens a new book, runs macros, saves and closes - but a save prompt is stopping it from working

Question

I have a macro in a book that opens up my separate "vessel book", runs a copy and paste type macro, saves, and then closes the book.

Everything works apart from when it comes to close, the book will prompt the pop-up "would you like to save, yes no etc", even though the macro has save written into it?

Therefore it doesn't actually close - this is really annoying as I would like to schedule it to happen everyday, and if it doesn't close then it won't be able to.

Apparently it is not possible to turn this prompt window off so any help would be great.

Please find my formula below.

Sub AllFiles()

    Application.Run "'G:\Research\Analysts\Elliot Frost\Barge_Tracker_Master.xlsm'!HistoricalData"
    Workbooks("Barge_Tracker_Master.xlsm").Close savechanges:=True

End Sub

'=============================================================

Sub Scheduler()

    '-- RUNS SUB(S) (OR FUNCTIONS) AT TIME SCHEDULED.

    Application.OnTime TimeValue("11:28:00"), "AllFiles"

End Sub

score 1 · Answer 1 · edited May 23 '17 at 12:33

1

Check the answer from this post:

Sub test()
   '[...]
    Application.DisplayAlerts = False
    ThisWorkbook.Save
    ThisWorkbook.Close False
    Application.DisplayAlerts = True
End Sub

edited May 23 '17 at 12:33

Community

1
1

answered Feb 09 '17 at 10:39

GôTô

7,974
3
32
43

Just FYI, the link for your site on your profile isn't working! ;) – R3uK Feb 09 '17 at 10:42
@R3uK ah right! That's an old one, thanks for letting me know :) – GôTô Feb 09 '17 at 10:43
Thanks a lot for your help! – elliot frost Feb 09 '17 at 10:50

R3uK · Accepted Answer · 2017-02-09T10:52:54.003

1

Add Application.DisplayAlerts = False, like this :

Sub AllFiles()

With Application
    .Run "'G:\Research\Analysts\Elliot Frost\Barge_Tracker_Master.xlsm'!HistoricalData"
    DoEvents
    .DisplayAlerts = False
End With

Workbooks("Barge_Tracker_Master.xlsm").Close savechanges:=True
Application.DisplayAlerts = True

Scheduler
End Sub

Sub Scheduler()

'-- RUNS SUB(S) (OR FUNCTIONS) AT TIME SCHEDULED.

Application.OnTime TimeValue("11:28:00"), "AllFiles"

End Sub

edited Feb 09 '17 at 10:52

answered Feb 09 '17 at 10:40

R3uK

14,417
7
43
77

Thanks a lot, works perfectly! Could you please confirm that my application.ontime will run everyday at that time? Thanks a lot – elliot frost Feb 09 '17 at 10:49
@elliotfrost : See http://www.cpearson.com/excel/OnTime.aspx, for proper implementation. I've already added the Scheduler at the end of AllFiles to rearm the OnTime after executing! So that should work already! ;) – R3uK Feb 09 '17 at 10:52
Thanks a lot, appreciate your help! – elliot frost Feb 09 '17 at 12:20
@elliotfrost : If the answers solve your issue, please accept one that helped to close your question and help others that might have the same issue find the answer quicker! – R3uK Feb 13 '17 at 11:36

I have a macro that opens a new book, runs macros, saves and closes - but a save prompt is stopping it from working

2 Answers2