Excel closing problem

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
LVL 1
bthouinAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieVBA ExpertCommented:
Can you post the code and/or upload a sample workbook?
0
Ejgil HedegaardCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
[ fanpages ]IT Services ConsultantCommented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Rory ArchibaldCommented:
Did you cancel the original save event (Cancel = True)?
0
bthouinAuthor Commented:
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
[ fanpages ]IT Services ConsultantCommented:
...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
bthouinAuthor Commented:
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
[ fanpages ]IT Services ConsultantCommented:
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
bthouinAuthor Commented:
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
[ fanpages ]IT Services ConsultantCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.