PDA

View Full Version : Code to close workbooks



Juriemagic
11-26-2015, 12:32 AM
Hi good people!

I have this code:
Private Sub Workbook_Open()
Dim WkbkName As Object
Application.ScreenUpdating = False
Application.EnableEvents = False
For Each WkbkName In Application.Workbooks()
If WkbkName.Name <> ThisWorkbook.Name Then WkbkName.Close savechanges:=True
Next
ThisWorkbook.Close
Application.Quit

End Sub


The code closes nicely BUT: IF changes to a file was made, I get a "save to location" prompt box, which I do not want. I really want this code to just save each file in its original destination, under original name. Other workbooks with no changes, should just close. (This part works).

Then lastly please, although I have application.quit in there, I still end up with a grey excel screen. Please be so kind to assist me with this problem?..Thank you all kindly..

Aflatoon
11-26-2015, 01:17 AM
Application.Quit won't run because you closed the workbook containing the code. (as was mentioned previously elsewhere)

You should only get prompted for a save destination for workbooks that haven't already been saved. What do you expect the code to do there?

Juriemagic
11-26-2015, 01:28 AM
The code is supposed to save all open workbooks, then close them, and then close itself. I have removed this part in the meantime:
ThisWorkbook.Close
, also have I changed:
WkbkName.Close savechanges:=True to
WkbkName.Save.

I have found the code to save and quit properly now if I have just one workbook opened. When I opened three workbooks, I got the error "save method failed"..

Would you please assist me with this?

Aflatoon
11-26-2015, 01:38 AM
I can't see why you would get a save error.

Can I ask why you need a separate workbook whose only purpose is to close all open workbooks? It seems very odd to me.

Juriemagic
11-26-2015, 01:53 AM
Yes, normally this is odd. I have a MASSIVE workbook, something like 16 sheets, and each one of them contain coding. I am using other coding to open this workbook in it's own instance, because if it is opened in the same instance as other workbooks, then, upon closing this workbook, it closes all other workbooks as well, although the excel application is still open. I have posted threads on more than one forum site regarding this, but just could not get any joy.

NOW, I have also setup a basic windows task to open this workbook on certain days and times, (BUT NOT in it's own instance),to edit a sheet, then save and close it. But because it interferes with other workbooks which might be open, I'd much rather be on the save side and have the code in this thread run just before all of the above happens, to ensure all other files (workbooks) are saved and closed.. This would be much easier, than (I think)..to try and find the reason for the "interference"...Hope this makes sense..

mancubus
11-26-2015, 02:10 AM
i like to post links to vbax kb articles...

http://www.vbaexpress.com/kb/getarticle.php?kb_id=718

Juriemagic
11-26-2015, 02:17 AM
mancubus,

thank you, I have tried this code...it does nothing...I have put it as a workbook open event instruction, but it simply ignores the already open workbooks..

Just tried it again..what it now does is giving "application-defined or object-defined" error..

mancubus
11-26-2015, 02:54 AM
it worked for me as expected.

which line, if any, is highlighted yellow when you hit Debug button?

a simplified version worked as well.


Sub vbax_54375_Save_Open_WBs_Quit_Excel()

Dim wb As Workbook

Application.DisplayAlerts = False

For Each wb In Workbooks
If Not wb.ReadOnly Then wb.Save
Next wb

Application.Quit

End Sub

Juriemagic
11-26-2015, 03:07 AM
Now it gives a different error...this beats me, the error now is "'save' of 'object_workbook' failed".. I'm going to try your simplified code..thanx for that..

Juriemagic
11-26-2015, 03:09 AM
Just tried your code, same error as in previous post. You know what, it's okay. I'm not going to be bothered about this anymore. thanx for your time..

Juriemagic
11-26-2015, 05:57 AM
Hi mancubus,

I found the reason why the code sometimes fails. I sometimes have excel files with extension "xls". These files are reports exported to Excel from our on-line system. It is these reports I cannot save and close. All other file extensions are not a problem. Sorry to wake this thing up again. Would you be willing to show me how to alter the code..I used your simple code, to close these files as well?

mancubus
11-26-2015, 07:04 AM
fortunately i have the opportunity to duplicate your case via our in-house application's "export to excel" button.

when hit the button, a workbook is opened in a new excel session which i belive is the same with your application.

and when i run the code this workbook remains open.

you should search ways to access other open excel sessions and Control them by the codes in the current excel session.

here is something to start with:

http://stackoverflow.com/questions/2971473/can-vba-reach-across-instances-of-excel

good luck as i dont know how to do this.

Juriemagic
11-26-2015, 11:37 PM
Thank you kindly for your assistance and honesty. I appreciate your time!...

Juriemagic
11-26-2015, 11:41 PM
Thank you kindly for your assistance and honesty. I appreciate your time!...

What I can say is that I do not get a new opened application, what happens here is the code simply errors, saying "save_method of range class failed". The
.Save is highlighted yellow. So, maybe this changes things?..maybe the code can be modified to look for the extensions and if an "xls" is found, just close it?..

Juriemagic
11-26-2015, 11:48 PM
Mancubus,

I got it sorted thank you, someone on MR Excel replied with the correct answer. Thank you for your time!!

mancubus
11-26-2015, 11:59 PM
you are welcome Juriemagic.

please post links to the threads opened in different help forums.
http://www.excelguru.ca/content.php?184

Juriemagic
11-27-2015, 12:07 AM
I will....apologies about that..