Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I force a workbook to close?
How can I force a workbook to close?
I run this code: ThisWorkbook.EnableAutoRecover = False ThisWorkbook.Saved = True ThisWorkbook.Close That triggers this: Private Sub Workbook_BeforeClose(Cancel As Boolean) If ThisWorkbook.name = "MainFile.xls" Then Application.Quit End If End Sub But that kills the whole Excel-application! If I open Excel and then goto Start Office Excel, I have 2 instances of Excel open. I can end one instance pretty easy. However, if I have two workbooks open within the SAME instance, Excel doesn’t seem to be able to close one file and keep the other open. In fact, Excel opens several new files, all named ‘MainFile.xls’. VERY ANNOYING!!! How can I shut down any file named ‘MainFile.xls’ and keep other Excel files open? Thanks! Ryan-- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I force a workbook to close?
Hi Ryan,
Run this code and enjoy. Sub Macro1() Windows("MainFile.xls").Activate ActiveWorkbook.Close End Sub On 22 Лип, 21:07, ryguy7272 wrote: How can I force a workbook to close? I run this code: Â* Â*ThisWorkbook.EnableAutoRecover = False Â* Â*ThisWorkbook.Saved = True Â* Â*ThisWorkbook.Close That triggers this: Private Sub Workbook_BeforeClose(Cancel As Boolean) If ThisWorkbook.name = "MainFile.xls" Then Â* Â* Â* Â* Application.Quit End If End Sub But that kills the whole Excel-application! Â*If I open Excel and then goto Start Office Excel, I have 2 instances of Excel open. Â*I can end one instance pretty easy. Â*However, if I have two workbooks open within the SAME instance, Excel doesnt seem to be able to close one file and keep the other open. Â*In fact, Excel opens several new files, all named €˜MainFile.xls. Â*VERY ANNOYING!!! How can I shut down any file named €˜MainFile.xls and keep other Excel files open? Thanks! Ryan-- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I force a workbook to close?
I've never seen excel open multiple files with the same name in the same
instance. And I bet if you check again, there's either a difference in names or you have multiple instances open. I'm not sure what you're doing, but if don't want to close excel, you could check to see how many workbooks are open in the _BeforeClose event. If Workbooks.Count 2 Then Might be sufficient to check??? On 07/22/2010 13:07, ryguy7272 wrote: How can I force a workbook to close? I run this code: ThisWorkbook.EnableAutoRecover = False ThisWorkbook.Saved = True ThisWorkbook.Close That triggers this: Private Sub Workbook_BeforeClose(Cancel As Boolean) If ThisWorkbook.name = "MainFile.xls" Then Application.Quit End If End Sub But that kills the whole Excel-application! If I open Excel and then goto Start Office Excel, I have 2 instances of Excel open. I can end one instance pretty easy. However, if I have two workbooks open within the SAME instance, Excel doesn’t seem to be able to close one file and keep the other open. In fact, Excel opens several new files, all named ‘MainFile.xls’. VERY ANNOYING!!! How can I shut down any file named ‘MainFile.xls’ and keep other Excel files open? Thanks! Ryan-- -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I force a workbook to close?
On Jul 22, 2:50*pm, Dave Peterson wrote:
I've never seen excel open multiple files with the same name in the same instance. *And I bet if you check again, there's either a difference in names or you have multiple instances open. I'm not sure what you're doing, but if don't want to close excel, you could check to see how many workbooks are open in the _BeforeClose event. * * *If Workbooks.Count 2 Then Might be sufficient to check??? On 07/22/2010 13:07, ryguy7272 wrote: How can I force a workbook to close? I run this code: * * ThisWorkbook.EnableAutoRecover = False * * ThisWorkbook.Saved = True * * ThisWorkbook.Close That triggers this: Private Sub Workbook_BeforeClose(Cancel As Boolean) If ThisWorkbook.name = "MainFile.xls" Then * * * * *Application.Quit End If End Sub But that kills the whole Excel-application! *If I open Excel and then goto Start *Office *Excel, I have 2 instances of Excel open. *I can end one instance pretty easy. *However, if I have two workbooks open within the SAME instance, Excel doesn’t seem to be able to close one file and keep the other open. *In fact, Excel opens several new files, all named ‘MainFile.xls’. *VERY ANNOYING!!! How can I shut down any file named ‘MainFile.xls’ and keep other Excel files open? Thanks! Ryan-- -- Dave Peterson- Hide quoted text - - Show quoted text - Thanks George and Dave! I tried both of your recommendations, and many others that i've seen on the Web. When I close Excel each time, no matter what I do, I always get a file poping up called 'MainFile.xls' and I'll get another file named 'MainFile(1).xls'...if I'm in the same instance of Excel. If I have TWO instances of Excel running, everything is perfect. This Excel behavior is highly unusual..... I've never seen anything like it... Any other ideas? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I force a workbook to close?
On Jul 22, 3:35*pm, ryguy7272 wrote:
On Jul 22, 2:50*pm, Dave Peterson wrote: I've never seen excel open multiple files with the same name in the same instance. *And I bet if you check again, there's either a difference in names or you have multiple instances open. I'm not sure what you're doing, but if don't want to close excel, you could check to see how many workbooks are open in the _BeforeClose event. * * *If Workbooks.Count 2 Then Might be sufficient to check??? On 07/22/2010 13:07, ryguy7272 wrote: How can I force a workbook to close? I run this code: * * ThisWorkbook.EnableAutoRecover = False * * ThisWorkbook.Saved = True * * ThisWorkbook.Close That triggers this: Private Sub Workbook_BeforeClose(Cancel As Boolean) If ThisWorkbook.name = "MainFile.xls" Then * * * * *Application.Quit End If End Sub But that kills the whole Excel-application! *If I open Excel and then goto Start *Office *Excel, I have 2 instances of Excel open. *I can end one instance pretty easy. *However, if I have two workbooks open within the SAME instance, Excel doesn’t seem to be able to close one file and keep the other open. *In fact, Excel opens several new files, all named ‘MainFile.xls’. *VERY ANNOYING!!! How can I shut down any file named ‘MainFile.xls’ and keep other Excel files open? Thanks! Ryan-- -- Dave Peterson- Hide quoted text - - Show quoted text - Thanks George and Dave! *I tried both of your recommendations, and many others that i've seen on the Web. *When I close Excel each time, no matter what I do, I always get a file poping up called 'MainFile.xls' and I'll get another file named 'MainFile(1).xls'...if I'm in the same instance of Excel. *If I have TWO instances of Excel running, everything is perfect. *This Excel behavior is highly unusual..... *I've never seen anything like it... Any other ideas?- Hide quoted text - - Show quoted text - I would use Application.Quit, but that just kills everything. I even tried dimming a new instance of Excel and assigning the problematic WB to that. This ended up opening several Excel files (some kind of loop) and it took me several minutes to close these multiple files (which kept opening again and again). |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I force a workbook to close?
Are you sure that the second, third, ... files are named:
mainfile(1).xls mainfile(2).xls If you had a template file (*.xlt or *.xltx or *.xltm or ...), then the name would look similar, but with no ()'s and no extension. They would look like: mainfile1 mainfile2 ======== But I've never seen these created by accident -- maybe you or a macro is creating a new workbook based on a different workbook. I think that the next time you see these files, you'll have to see where they come from. You can do that by opening the VBE and typing this into the immediate window: ?workbooks("mainfile(1).xls").fullname (Change the name to what you see in the project explorer of the VBE.) If you don't see a path/folder, then that means these files have never been saved. If you see a path, then the files have been saved at least once before -- and something is opening them. But the bad news is that I don't see anything in the short snippets of code that you've posted that give any clues. The next thing I would do is to open excel in safe mode: Close excel Windows start button|Run type: Excel /safe File|Open your workbook This will open excel with a bunch of stuff turned off -- including macros. Then you can play around to see if you can get those other files to open/be created. ========== One or two more questions: Is there a workbook_beforesave event going on? Is that mainfile really a template file so when you do a Save, you're saving that template file as mainfile1.xls? On 07/22/2010 14:40, ryguy7272 wrote: On Jul 22, 3:35 pm, wrote: On Jul 22, 2:50 pm, Dave wrote: I've never seen excel open multiple files with the same name in the same instance. And I bet if you check again, there's either a difference in names or you have multiple instances open. I'm not sure what you're doing, but if don't want to close excel, you could check to see how many workbooks are open in the _BeforeClose event. If Workbooks.Count 2 Then Might be sufficient to check??? On 07/22/2010 13:07, ryguy7272 wrote: How can I force a workbook to close? I run this code: ThisWorkbook.EnableAutoRecover = False ThisWorkbook.Saved = True ThisWorkbook.Close That triggers this: Private Sub Workbook_BeforeClose(Cancel As Boolean) If ThisWorkbook.name = "MainFile.xls" Then Application.Quit End If End Sub But that kills the whole Excel-application! If I open Excel and then goto Start Office Excel, I have 2 instances of Excel open. I can end one instance pretty easy. However, if I have two workbooks open within the SAME instance, Excel doesn’t seem to be able to close one file and keep the other open. In fact, Excel opens several new files, all named ‘MainFile.xls’. VERY ANNOYING!!! How can I shut down any file named ‘MainFile.xls’ and keep other Excel files open? Thanks! Ryan-- -- Dave Peterson- Hide quoted text - - Show quoted text - Thanks George and Dave! I tried both of your recommendations, and many others that i've seen on the Web. When I close Excel each time, no matter what I do, I always get a file poping up called 'MainFile.xls' and I'll get another file named 'MainFile(1).xls'...if I'm in the same instance of Excel. If I have TWO instances of Excel running, everything is perfect. This Excel behavior is highly unusual..... I've never seen anything like it... Any other ideas?- Hide quoted text - - Show quoted text - I would use Application.Quit, but that just kills everything. I even tried dimming a new instance of Excel and assigning the problematic WB to that. This ended up opening several Excel files (some kind of loop) and it took me several minutes to close these multiple files (which kept opening again and again). -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I force a workbook to close?
On Jul 22, 4:46*pm, Dave Peterson wrote:
Are you sure that the second, third, ... files are named: mainfile(1).xls mainfile(2).xls If you had a template file (*.xlt or *.xltx or *.xltm or ...), then the name would look similar, but with no ()'s and no extension. They would look like: mainfile1 mainfile2 ======== But I've never seen these created by accident -- maybe you or a macro is creating a new workbook based on a different workbook. I think that the next time you see these files, you'll have to see where they come from. You can do that by opening the VBE and typing this into the immediate window: ?workbooks("mainfile(1).xls").fullname (Change the name to what you see in the project explorer of the VBE.) If you don't see a path/folder, then that means these files have never been saved. If you see a path, then the files have been saved at least once before -- and something is opening them. But the bad news is that I don't see anything in the short snippets of code that you've posted that give any clues. The next thing I would do is to open excel in safe mode: Close excel Windows start button|Run type: Excel /safe File|Open your workbook This will open excel with a bunch of stuff turned off -- including macros.. Then you can play around to see if you can get those other files to open/be created. ========== One or two more questions: Is there a workbook_beforesave event going on? Is that mainfile really a template file so when you do a Save, you're saving that template file as mainfile1.xls? On 07/22/2010 14:40, ryguy7272 wrote: On Jul 22, 3:35 pm, *wrote: On Jul 22, 2:50 pm, Dave *wrote: I've never seen excel open multiple files with the same name in the same instance. *And I bet if you check again, there's either a difference in names or you have multiple instances open. I'm not sure what you're doing, but if don't want to close excel, you could check to see how many workbooks are open in the _BeforeClose event. * * * If Workbooks.Count *2 Then Might be sufficient to check??? On 07/22/2010 13:07, ryguy7272 wrote: How can I force a workbook to close? I run this code: * * *ThisWorkbook.EnableAutoRecover = False * * *ThisWorkbook.Saved = True * * *ThisWorkbook.Close That triggers this: Private Sub Workbook_BeforeClose(Cancel As Boolean) If ThisWorkbook.name = "MainFile.xls" Then * * * * * Application.Quit End If End Sub But that kills the whole Excel-application! *If I open Excel and then goto Start * *Office * *Excel, I have 2 instances of Excel open. *I can end one instance pretty easy. *However, if I have two workbooks open within the SAME instance, Excel doesn’t seem to be able to close one file and keep the other open. *In fact, Excel opens several new files, all named ‘MainFile.xls’. *VERY ANNOYING!!! How can I shut down any file named ‘MainFile.xls’ and keep other Excel files open? Thanks! Ryan-- -- Dave Peterson- Hide quoted text - - Show quoted text - Thanks George and Dave! *I tried both of your recommendations, and many others that i've seen on the Web. *When I close Excel each time, no matter what I do, I always get a file poping up called 'MainFile.xls' and I'll get another file named 'MainFile(1).xls'...if I'm in the same instance of Excel. *If I have TWO instances of Excel running, everything is perfect. *This Excel behavior is highly unusual..... *I've never seen anything like it... Any other ideas?- Hide quoted text - - Show quoted text - I would use Application.Quit, but that just kills everything. *I even tried dimming a new instance of Excel and assigning the problematic WB to that. *This ended up opening several Excel files (some kind of loop) and it took me several minutes to close these multiple files (which kept opening again and again). -- Dave Peterson Thanks George and Dave!! Got it working!! I had some help from a colleague in my office. Thanks Mandeep!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Force Excel to close | Excel Programming | |||
How to force Excel wkbk to close | Excel Programming | |||
How do you force excel to save on close | Excel Discussion (Misc queries) | |||
Is there a way to force Excel to close workbooks independently? | Excel Discussion (Misc queries) | |||
Force a Message Box to close unanswered | Excel Programming |