Solved

Excel closing problem

Posted on 2014-09-19
10
168 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 21

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
 
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now