Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 183
  • Last Modified:

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
0
bthouin
Asked:
bthouin
2 Solutions
 
NorieData ProcessorCommented:
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
 
[ 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
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!

 
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now