Excel workbooks close зависает
It works absolutely fine when only this this single workbook is open. But when I open another workbook and then try to close my "test workbook" it closes properly without saving. but only a second after that the whole Excel crashes!
Can you help me solving this problem?
Answers
Of course that Public Sub statement must be in the current open workbook. so this isn't the best either.
Yes, that's a problem :( Any other ideas?
Make it think that it has already been saved by setting the Saved property to True and it will close without saving and without an alert message.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Saved = True
End Sub
Added with Edit: Ensure that you save it before closing the first time or the code will not be saved.
All replies
Excel doesn't know which ThisWorkbook you are talking about since you have two open.
I suspect you may be setting up an infinite event loop: this event is called some time after the command to close the workbook has been triggered, but before Excel's other Close handling events. In the handler, you're telling the workbook to Close again, which very well might be triggering the _BeforeClose event again, which calls your handler again, which tells the workbook to Close again, which triggers _BeforeClose again.
This problem is likely happening whether you have another workbook open or not, it's just that with another workbook open, you notice the problem because you want Excel to stay open but it's not able to.
What are you trying to accomplish here? It looks like you're trying to make sure that every time the "ThisWorkbook" closes it never saves changes?
Will ThisWorkbook close before the CloseNoSave Sub finishes execution, leaving Application.EnableEvents set to False? It seems like it'd be more stable to put the CloseNoSave routine in, say, an add-in.
Technically you are correct. Addins are the best spot for this sort of functionality that affects major event handling. Also, in Addins, you always refer to the ActiveWorkbook or a handle to one.
Note: If another workbook is open, then my code will have shut-off event processing. not good. And if you open a new workbook, it's Open event handler will be shut-off. Again, not good.
A wonky workaround to your current problem:
Use Application.OnTime to schedule the execution of a Public Sub with a 1 second delay.
Place this code right before the Workbook.Close statement.
The Public Sub will have nothing but the one-liner:
Of course that Public Sub statement must be in the current open workbook. so this isn't the best either.
I have following code under a button. When clicked it just closes the current Excel sheet but not the entire Excel application.
Note: I don't have any other sheets open.
The following window still appears.
12 Answers 12
I had this issue and I resolved it by putting in the Workbook_BeforeClose() :
This is more than likely the answer, because it sounds like you have a dialog that isn't being dealt with and is therefore preventing the application from quitting. This will prevent a save dialog from appearing during the workbook closing.
remove the Application.DisplayAlerts = True from the routine.
from the help for Application.Quit Method :
If unsaved workbooks are open when you use this method, Microsoft Excel displays a dialog box asking whether you want to save the changes. You can prevent this by saving all workbooks before using the Quit method or by setting the DisplayAlerts property to False. When this property is False, Microsoft Excel doesn’t display the dialog box when you quit with unsaved workbooks; it quits without saving them.
This will avoid any (possibly hidden) prompts from stopping excel from closing completely
my excel will open in read only as it is a public file. So i dont want any prompt ! I have ur suggesstion but same result. Probably this is not my answer
I experienced the same issue and was able to resolve the issue with code that looks to see if multiple workbooks are open or not .
When Application.Quit is encountered in a subroutine, it will only stay in memory and continue to run lines under it and will actually quit until it encounters a "Exit Sub". When the normal "End Sub" at the primary level is encountered, it will then also close Excel. But say if the workbook is somehow closed before reaching the "Exit Sub", "End" or "End Sub" line, then Excel will not close.
Solution is to create a Public variable called ToQuitNow with initial False value and change it to True where you want Excel to quit. and test right after to see if it is true, then return to previous Sub level by "Exit Sub" or "End" to quit right away, and do the same at every subrountine level where it is expected to return from the deeper subroutine. When it gets back to the primary level, then a final "Exit Sub" will actually terminates Excel. If you do not want Excel to ask for saving changes made, add line "ThisWorkbook.Saved = True" right after Application.Quit, or before the final "Exit Sub" at the Primary level and Excel will quit without saving.
Try the following test below, just run "Test"
The window does not close because you are using personal.xlsb. Cut Personal.xlsb and paste in another location.
Instead of Personal.xlsb create and work on modules. It's a better option.
"ThisWorkbook.Saved = True" after "Application.Quit" works on Excel 2016
I did not try it, but maybe this will help:
According to Norie you might not have anymore workbooks open, therefore Application.Quit will never be executed.
AlphaFrog therefore suggests this:
I had the same issue using the following code closed excel cleanly:
This will allow excel to cleanly close without keeping a "ghost" window open.
This worked for me: (Office 365)
This is a strange one, hopefully someone will find this answer useful. I ran into something very similar using Excel 2010 (14.0). I stumbled to my answer through experimentation. This is bad answer for general purpose.
For whatever reason Application.Quit fails silently if the option AccessVBOM is not enabled. It is not enabled out of the box and can be set/unset by your network admin by windows policy.
You can find this option in the GUI by traversing "Excel Options" -> "Trust Center" -> "Trust Center Settings" -> "Macro Settings" -> "Trust access to the VBA project object model". Or programmatically.
У меня есть следующий код под кнопкой. При нажатии он просто закрывает текущий лист Excel, но не все приложение Excel.
Примечание: у меня нет других открытых листов.
Следующее окно все еще появляется.
У меня была эта проблема, и я решил ее, добавив Workbook_BeforeClose() :
Это более чем вероятный ответ, потому что похоже, что у вас есть диалог, который не обрабатывается и, следовательно, предотвращает выход приложения. Это предотвратит появление диалогового окна сохранения во время закрытия книги.
убрать Application.DisplayAlerts = True из распорядка.
из справки по Application.Quit Method :
If unsaved workbooks are open when you use this method, Microsoft Excel displays a dialog box asking whether you want to save the changes. You can prevent this by saving all workbooks before using the Quit method or by setting the DisplayAlerts property to False. When this property is False, Microsoft Excel doesn’t display the dialog box when you quit with unsaved workbooks; it quits without saving them.
Это позволит избежать любых (возможно, скрытых) подсказок, не позволяющих полностью закрыть Excel.
мой Excel будет открыт только для чтения, поскольку это общедоступный файл. Так что я не хочу никаких подсказок! У меня есть ваше предложение, но результат тот же. Наверное это не мой ответ
У меня возникла та же проблема, и я смог решить ее с помощью кода, который проверяет, открыты ли несколько книг или нет .
Когда в подпрограмме встречается Application.Quit, он остается только в памяти и продолжает выполнять строки под ним и фактически завершает работу до тех пор, пока не встретит «Exit Sub». При обнаружении обычного «End Sub» на первичном уровне он также закроет Excel. Но скажем, если книга каким-то образом закрывается до того, как дойдет до строки «Выход из подпрограммы», «Конец» или «Конец подпрограммы», тогда Excel не закроется.
Решение состоит в том, чтобы создать общедоступную переменную ToQuitNow с начальным значением False и изменить ее на True в том месте, где вы хотите завершить работу Excel. и проверьте сразу после этого, чтобы убедиться, что это правда, затем вернитесь на предыдущий подуровень с помощью «Exit Sub» или «End», чтобы сразу выйти, и сделайте то же самое на каждом подпрограмме, где ожидается возврат из более глубокой подпрограммы. Когда он возвращается на основной уровень, последний «Exit Sub» фактически завершает работу Excel. Если вы не хотите, чтобы Excel запрашивал сохранение внесенных изменений, добавьте строку «ThisWorkbook.Saved = True» сразу после Application.Quit или перед последним «Exit Sub» на основном уровне, и Excel выйдет без сохранения.
Сразу скажу: решение проблемы не требуется, я его сам нашёл.
Баг проявляется в разных версиях Excel (от 2003 до 2013)
Что надо сделать, чтобы подвис Excel
(причем, подвисание частичное, - можно продолжать работу с файлами, закрывать и сохранять их, - просто Excel загружает процессор на полную,
и полностью зависает при попытке закрытия приложения)
1) создаем форму, в коде формы объявляем переменную Public SpecialControlsCollection As Collection
2) при инициализации формы, добавляем в неё экземпляры класса, обрабатывающего события элементов управления на форме:
Private Sub UserForm_Initialize()
Dim SC As SpecialControl
Set SC = New SpecialControl: Set SC.NumericTexbox = Me.TextBox_SourceData_MinValue: Me.SpecialControlsCollection.Add SC
Set SC = New SpecialControl: Set SC.NumericTexbox = Me.TextBox_SourceData_MaxValue: Me.SpecialControlsCollection.Add SC
Set SC = New SpecialControl: Set SC.NumericTexbox = Me.TextBox_SourceData_Step: Me.SpecialControlsCollection.Add SC
3) запускаем форму, - всё работает
4) закрываем форму крестиком, - всё нормально, Excel не виснет
5) щелкаем по любой ячейке листа, - Excel начинает отъедать все ресурсы процессора.
В этот момент никакие макросы не работают (можно даже закрыть все открытые в Excel файлы) - зависание не прекратится
Решается проблема добавлением одной строки кода:
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Set Me.SpecialControlsCollection = Nothing
End Sub
Понимаю, что объектные переменные желательно обнулять, - но, по идее, при закрытии формы все само должно уничтожаться корректно.
Открываем файл, нажимаем кнопку для запуска формы, закрываем форму, щелкаем по любой ячейке, - Excel подвис
(файл можно закрыть, а само приложение Excel - никак)
Эта цепочка заблокирована. Вы можете просмотреть вопрос или оставить свой голос, если сведения окажутся полезными, но вы не можете написать ответ в этой цепочке.
This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.
Answered by:
Question
Here is my code:
Thanks for any help!
Answers
Ok, i like to finish this thread, as it took me so much time now. I really thank you for your help.
The only working solution I found, is to kill the process if it keeps hanging.
This was the only thing that worked for me (and many others :-) )
This is the link I used:
All replies
Join the club. I'm not sure why you need to Marshal the handle objects. oExcel is an application already so you don't need to use Application again. Also Workbooks.Close is closing all the workbooks without killing the application. Once the workbooks are closed the Net Library is no longer linked to the application so you can't kill the application. Try the code below. One problem that I always have is when I'm debugging a VSTO application and I restart the my program the excel application doesn't close. so I havve to open the task managger and manually kill the excel applications. Sometimes I have 8 to 10 excel applications running that I have to kill.
oWorkbook = null;
oExcel . Quit ();
oExcel = null ;
thanks for your answer. I tried your suggestion, but it still doesn´t work.
I tried everything now, setting all objects to null, closing in different order, releasing them with the code shown above, but none of them works.
I will create code to kill the process in task manager the dirty way.
This same issue is discussed over here.
I hope this helps you.
If this post answers your question, please click "Mark As Answer". If this post is helpful please click "Mark as Helpful".
This is the same what I am doing. There is extra object for everything and no 2 dots.
I found out that as long as I just open Excel and close it, the process disappears. But when I access a cell in Excel and write something in, the process won´t disappear.
That´s the code for accessing a cell:
You should call GC.Collect, after your last call of Marshal.ReleaseComObject as Kb suggests. Reason for this is, there might be any references to RCW still holding. Calling GC.Collect forces to release any references.
I hope this help you.
If this post answers your question, please click "Mark As Answer". If this post is helpful please click "Mark as Helpful".
Make sure all excel applications are closed before yo utry it one more time. Stop Visual Studio. Close all excel applications. then try one more time with my recommended changes. I know it works. You may be accidently opening more than one excel application in your software and then only closing one which makes it look like the excel application isn't closing.
Sorry I didn't catch the problem sooner. No need to do any of the marshal stuff or the releases. they are not necessary. when you modify the workbook excel won't normally close without the user being prompted if they want to save changes. So simply end you code like this
If you don't want to save changes
boolean savechanges = false;
oWorkbook . Close ( savechanges, Type.Missing, Type.Missing );
oWorkbook = null;
oExcel . Quit ();
oExcel = null ;
Or this if yo do want to save changes
boolean savechanges = true;
oWorkbook . Close ( savechanges, Type.Missing, Type.Missing );
oWorkbook = null;
oExcel . Quit ();
oExcel = null ;
I got it working now with your suggestion AND with the Marshal things. Without the GC.Collect() and Releasing of objects it still doesn´t work.
But I have another strange behavoir. I have 2 normal buttons on my form. I created the Click() events of both. When I post the exactly same code in both click events, it works on one button and it doesn´t at the other. It´s really the same code and 2 normal buttons. At the not working button there are some SQL queries AFTER the Excel code. But for testing I created a stop point just after the Excel code, to see if my code has something to do with the problem. But still the same.
How can that be?
It's great that you solved your original problem. related to Excel programming question, will be well addressed from
I hope this helps you.
If this post answers your question, please click "Mark As Answer". If this post is helpful please click "Mark as Helpful".
Just to let you know I'm one of the top people who answer questions about excel at the other site. I suspect that yo are opening more that one excel application. the two buttons are problem in different classes. I alway use the same variable for common applications
public static Excel.Application xlsapp = null
public static void funct1()
if (xlsppp == null)
open you application here
public static void funct2()
if (xlsppp == null)
open you application here
Thanks, but it is definitely the only point where I create an Excel application.
I think it has something to do with a SQL query that is done just beforce the Excel things.
There are 2 SQL Commands inside a "if" check, if I delete one, it works fine. When both are inside, Excel keeps hanging.
Even if the if check skips (. ), Excel.exe keeps hanging. I get the feeling, that the code is "too long".
This is the code:
Isn´t that weird?
Excel.Application oExcel = new Excel.Application();
oExcel.Visible = true;
Excel.Workbooks oWorkbooks = oExcel.Workbooks;
string Pfad = "C:\test.xlsx";
Excel.Workbook oWorkbook = oWorkbooks.Open(Pfad);
Excel.Worksheet oSheet = oWorkbook.Sheets[1];
oSheet.Cells[1, 1] = "Test";
oWorkbook.Close(false, Type.Missing, Type.Missing);
oExcel.Application.Quit();
oExcel = null;
Ok, no matter what I´ve tried, it does not work at all. I found out, that when I place the Excel code in an seperate function, it works.
Then I write some data to a cell - and it works. Then to a second cell - and it works. Then to a third cell - and it keeps hanging.
I think it´s a bug in VS2010 and there is no solution.
When I don´t put the Excel code in a seperate function, it works until you have no other code in. When you have e.g. 20 lines of code before, it keeps hanging, but when you only have 19 lines, it works. I really tried this. No matter what code you write, you can even just declare a variable. At the moment when you reach a certain amount of lines before the Excel code, it works.
It must be a bug.
Ok, i like to finish this thread, as it took me so much time now. I really thank you for your help.
The only working solution I found, is to kill the process if it keeps hanging.
This was the only thing that worked for me (and many others :-) )
This is the link I used:
If the SQL code is keeping the conection then you need to fix dispose the connection. I would put the SQL code in a class and make sure you list the class as IDISPOSE. The finalize/close the SQL class before closing the excel application.
Object in Visual Studio are three types
1) Managed : Object On your program stack and execution stack and visual studio will make sure the stack is alligned and object released automatically
2) UnManaged : Object On your program stack and execution stack and visual studio will not make sure the stack is alligned unless you use Marshal. Calls to dll functions are in this category
3) Other : Applications that run in there own environments that Visual studio doesn't have access to there stacks. Applications like the Microsft Jet and Excel applications arre in these categories and adding the marshal code doesn'tt help solve these problems.
Yes, I know I forgot to dispose the connection. But why does this happen even when the code is skipped and not executed?
I did exactly as you have done here pretty much except I am creating a workbook from scratch and using Office 2007 and up which apparently has some clean-up code built in. However if you are still having troubles I would definitely be interested in and around the oSheet.Cells. code as per;
Also this was quite an interesting read on the subject of COM Interop
( In this post I will give an introduction to COM Interop and covering some of the basic concepts you need to understand when dealing with VSTO and the Office Object Model.)
I had the same problem and when setting the "SaveChanges" object to false it corrected my problem.
oWorkbook.Close(false, Type.Missing, Type.Missing);
Sorry for this extremely late reply. But I thought it would be helpful for people experiencing this problem in the future.
I was using COM and C++ using Visual Studio 2013 with Excel 2003 and noticed this same problem. Everything you wrote above is correct, except you are missing one very important line before the call to Quit().
oExcel.Application.SetUserControl(TRUE); // this must be called before a call to Quit otherwise the process will appear to close on screen but will still be lingering in your Windows Task Manager.
After you call that, then you may safely call oExcel.Application.Quit()
But, I had code that released each RCW and I was running GC. I tried both ReleaseComObject and FinalReleaseComObject. I was doing that in the same function that used Excel and did so outside the "try" block where I started Excel and did all the work with their data model. I figured all my objects were out of scope by then as I called Marshal.ReleaseComObject on each object in that code block. Apparently not so. I moved the call to run GC from the method that used Excel to an event handler that called that method. Finally, Excel was terminated. FinalReleaseComObject wasn't needed but I still called ReleaseComObject.
Читайте также: