How can I force a workbook to close?

[ryguy7272]

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--

 

[George]

Hi Ryan,

Run this code and enjoy.

Sub Macro1()
Windows("MainFile.xls").Activate
ActiveWorkbook.Close
End Sub

 

[Dave Peterson]

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???

 

[ryguy7272]

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 innames 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???

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?

 

[ryguy7272]

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]

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?

 

[ryguy7272]

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?

Thanks George and Dave!! Got it working!! I had some help from a
colleague in my office.
Thanks Mandeep!!