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
145 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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
 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
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.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

707 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