Solved

Excel closing problem

Posted on 2014-09-19
10
174 Views
Last Modified: 2014-09-23
Hi

I have developed an Excel (2013) template with lots of VBA code inside. A small part of this VBA is executed inn the workbook BeforeClose event, where the VBA code asks the user for the name of the file to save, making a suggestion for path and name, using the Application.GetSaveAsFilename method, because a) I want the file to be saved as .xlsm, if possible in a given directory; b) because I have to store the file path and name in a database. When the user has accepted/chosen path and name, I use ActiveWorkbook.SaveAs to save the file,  and then I store its path+ name in the database. So far so good.

However, AFTER all that, Excel still comes up with yet another dialog box, asking if the user wants to save his/her changes under the same file name (again). Is there a way to prevent Excel to come up with that dialog box, and instead to have Excel to just close the application ? I've tried adding Application.EnableEvents = False, but to no avail. There are 2 problems with that dialog box:
1) it's not needed anyway
2) depending on which button one presses, Excel saves the file again, but under the name "False.xlsm" in the same directory, and that name lands also in the database, which is no good at all

Thanks for help
Bernard
0
Comment
Question by:bthouin
10 Comments
 
LVL 33

Expert Comment

by:Norie
ID: 40333280
Can you post the code and/or upload a sample workbook?
0
 
LVL 22

Accepted Solution

by:
Ejgil Hedegaard earned 400 total points
ID: 40333792
Saving sets the workbook saved property to True, but since it is not, some action must happen in the workbook after saving.
You can set it just before the workbook BeforeClose event ends, with the code Me.Saved = True, then the workbook should close without asking to save.
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 40334350
Hi Bernard,

1)
Instead of 'Application.EnableEvents = False' use 'Application.DisplayAlerts = False' (& then reset to True after the save statement).  This will not prompt the overwriting of an existing file.

2)
As imnorie suggested, posting the relevant code will help us establish why the filename is being set to 'False'.
0
Industry Leaders: 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!

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 40336106
Did you cancel the original save event (Cancel = True)?
0
 
LVL 1

Author Comment

by:bthouin
ID: 40336128
Hi all

Before logging in to EE just now, I tried Ejgil's suggestion, and it works brilliantly. Actually I used:

ThisWorkbook.Saved = True

rather then Me.Saved = True because "Me" sounded rather like Access or forms-related than being in the Workbook Close event, but maybe both work.

@Rory: wouldn't setting Cancel to True stop the close, and then I'd have to close again, which would get me in a endless loop ?
0
 
LVL 35

Assisted Solution

by:[ fanpages ]
[ fanpages ] earned 100 total points
ID: 40336493
...did you need Application.DisplayAlerts to be set to False (to hide the prompt to overwrite an existing file), & is the filename still being set to False?
0
 
LVL 1

Author Comment

by:bthouin
ID: 40336842
Hi fanpages

No, I did not set DisplayAlerts anymore as I used Ejgil suggestion, which solves the prroblem, and there is no more False.xlsm generation now as Excel closes nicely after the file is saved at the proper place with the proper name.
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 40338001
OK, thanks for replying.

I can understand why setting ThisWorkbook.Saved would resolve the issue with prompting to (re-)save the workbook, but without seeing your code in the Workbook_BeforeClose event it is difficult to visualise why the additional issue is now resolved also.

If you find after further use that this is not the case, please do reply again.
0
 
LVL 1

Author Comment

by:bthouin
ID: 40338548
Hi

>> why the additional issue is now resolved also<<
My only real issue was the prompt for re-saving, which as a consequence was then creating sometimes this False.xlsm file (depending on the answer to the prompt. And honestly, I'm not really interested in finding out why that extra file was created. As the cause (the re-saving prompt) has disappeared, it won't be created anymore, so my problem is solved. Thanks for your help anyway.
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 40338682
OK... but in that case, I am not sure why you indicated my comment assisted you in the solution.

It seems my suggestion had no bearing at all.
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

733 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