Solved

What do I need to add my existing code so that it will save all spreadsheets when quitting Excel?

Posted on 2009-05-14
17
140 Views
Last Modified: 2012-05-07
Hello,

Below I have attached some code that will quit Excel at a specified time. What additional piece of code do I need to add so that all open excel files will be saved before the application is closed?

Thanks,

f19l
Sub open_bbg_files_test()

 

Application.OnTime TimeValue("12:39:00"), "ENDMACRO"

 

End Sub

 

Private Sub ENDMACRO()

 

Application.OnTime TimeValue("12:39:00"), "ENDMACRO"

Workbooks.Save

Application.Quit

End Sub

Open in new window

0
Comment
Question by:f19l
  • 9
  • 7
17 Comments
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 24383995
Try:

Chris
Private Sub ENDMACRO()

Dim wb As Workbook

'Application.OnTime TimeValue("12:39:00"), "ENDMACRO"
 

For Each wb In Application.Workbooks

    Application.DisplayAlerts = False

    wb.Save

    Application.DisplayAlerts = True

Next

Application.Quit

End Sub

Open in new window

0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 24384003
Apologies commented out a line for a test:

Chris
Private Sub ENDMACRO()

Dim wb As Workbook
 

    Application.OnTime TimeValue("12:39:00"), "ENDMACRO"
 

    Application.DisplayAlerts = False

    For Each wb In Application.Workbooks

        wb.Save

    Next

    Application.DisplayAlerts = True

    Application.Quit
 

End Sub

Open in new window

0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 24384021
Chris,
Isn't the DisplayAlerts redundant?
Cheers
Dave
 
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 24384047
Excuse my questioning but the ontime would seem to be in error I presume you want to stop it

Chris
Private Sub ENDMACRO()

Dim wb As Workbook

 

    Application.OnTime TimeValue("12:39:00"), "ENDMACRO", Schedule:=False

 

    Application.DisplayAlerts = False

    For Each wb In Application.Workbooks

        wb.Save

    Next

    Application.DisplayAlerts = True

    Application.Quit

 

End Sub

Open in new window

0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 24384095
Dave, perhaps so but it's a habit I am gaining even though in this case the save has of course occurred ... easier to get a good habit than have to remember to add it during close operations. ;o)

Chris
0
 

Author Comment

by:f19l
ID: 24384382
Chris,

This is how a previous expert provided me with the code. Basically I have a number of spreadsheets that have links to a system. In order to be able to extract the information there can be no macros running in the back ground. Therefore what I decided to do was that once the files have been opened all macros stop running thus allowing the file links to update. After about 5 minutes the Excel application will shut down with all open files saved.

0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 24384445
Yes I understand but:

Sub open_bbg_files_test()
Application.OnTime TimeValue("12:39:00"), "ENDMACRO"
End Sub
 
Triggers the timer, which calls endmacro, unfortunately (as I understand it) in endmacro you trigger the macro again therefore all the sheets will close then at the alloted time the spreadsheet will re-open.

I presume once the endmacro is called you are finished and can leave the exit to occur but without a re-open.  I suspect you or the expert overlooked the schedule = false BUT of course it's up to you as you know your application better than I.

Chris
0
 

Author Comment

by:f19l
ID: 24384684
Actually once the files have been updated I will then need to reopen these files, using a task scheduler, and start using the extracted data. Ideally what I would like is that instead of setting a fixed time I could have the code in place so that ten minutes after the files have been opened ENDMACRO is activated and all the files shut down as stated above.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 24386961
I suggest that once this question is finished to your satisfaction ... i.e. working then we can look to the next 'wish'.

Chris
0
 

Author Comment

by:f19l
ID: 24397312
When I run the entire code as shown below I get an error message realting to the application.ontime.

Sub closing_files()

Application.OnTime TimeValue("16:08:00"), "ENDMACRO2"

End Sub

Private Sub ENDMACRO2()
Dim wb As Workbook
 
    Application.OnTime TimeValue("16:08:00"), "ENDMACRO2", Schedule:=False
 
    Application.DisplayAlerts = False
    For Each wb In Application.Workbooks
        wb.Save
    Next
    Application.DisplayAlerts = True
    Application.Quit
 
End Sub
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 24398151
The problem is that the time is then wrong, which of course should have been obvious to me.

What do you want to happen in ENDMACRO2, i.e. why was the original entry there, since it wass setting it up to happen the next day automatically whereas I suspect you don't want the line at all?

Chris
0
 

Author Comment

by:f19l
ID: 24409454
The code used to work fine but no longer. All I want to happen in endmacro2 is that at the specified time every single workbook is saved and then shut down.
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 24409865
Then I suggest there is no need for a call to ontimer in ENDMACRO, i.e. as below:

Chris
Private Sub ENDMACRO()

Dim wb As Workbook

 

'    Application.OnTime TimeValue("12:39:00"), "ENDMACRO", Schedule:=False

 

    Application.DisplayAlerts = False

    For Each wb In Application.Workbooks

        wb.Save

    Next

    Application.DisplayAlerts = True

    Application.Quit

 

End Sub

Open in new window

0
 

Author Comment

by:f19l
ID: 24413242
But then where do I set the time?
0
 

Author Comment

by:f19l
ID: 24413307
The purpose of all of this is that once the initial processes have finished and have had time to update the ENDMACRO function will initiate at a specified time, save and close all workbooks and Excel.
0
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 500 total points
ID: 24413910
The timed call to the save sub in ENDMACRO is triggered when you call

Sub open_bbg_files_test()
    Application.OnTime TimeValue("12:39:00"), "ENDMACRO"
End Sub

Chris
0
 

Author Comment

by:f19l
ID: 24432919
Thanks, its all working now.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now