?
Solved

Excel closing problem

Posted on 2014-09-19
10
Medium Priority
?
179 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
[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
10 Comments
 
LVL 34

Expert Comment

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

Accepted Solution

by:
Ejgil Hedegaard earned 1600 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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

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!

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
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 demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

752 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