I am creating a macro to do some pretty wacky filtering. Some of the ways
That I have it doing it is through pasting onto new workbooks. SO, after
I've reached the end result (which it seems to be working nicely) I have two
extra documents open and I wanted to incorporate into my macro to close them
automatically. I went ahead and recorded a macro of closing those two
documents, and this is what it looked like:

Windows("Book1").Activate
ActiveWindow.Close
Windows("Original Filename").Activate
ActiveWindow.Close

Which effectively leaves me on Book 2 with the finished product. SO, I just
want everything else to close. That above formula would work nicely on the
file it was recorded for, BUT, when I go to use this macro on new documents
and data, it won't work the same because the original filename will be
different. Any ideas??? Thanks!!!

for each bk in application.Workbooks
if bk.Windows(1).Visible then
if bk.Name < "Name of book not to close" then
bk.close SaveChanges:=False
end if
end if
Next

--
Regards,
Tom Ogilvy


"bodhisatvaofboogie" wrote:

I am creating a macro to do some pretty wacky filtering. Some of the ways
That I have it doing it is through pasting onto new workbooks. SO, after
I've reached the end result (which it seems to be working nicely) I have two
extra documents open and I wanted to incorporate into my macro to close them
automatically. I went ahead and recorded a macro of closing those two
documents, and this is what it looked like:

Windows("Book1").Activate
ActiveWindow.Close
Windows("Original Filename").Activate
ActiveWindow.Close

Which effectively leaves me on Book 2 with the finished product. SO, I just
want everything else to close. That above formula would work nicely on the
file it was recorded for, BUT, when I go to use this macro on new documents
and data, it won't work the same because the original filename will be
different. Any ideas??? Thanks!!!

hmmm...it looks to be trying to work, but I can't seem to get it to do what
it is supposed to. Essentially nothing is happening though. No errors are
popping up. Maybe I did something wrong. Break it down simply for me
please!!! :)

"Tom Ogilvy" wrote:

for each bk in application.Workbooks
if bk.Windows(1).Visible then
if bk.Name < "Name of book not to close" then
bk.close SaveChanges:=False
end if
end if
Next

--
Regards,
Tom Ogilvy


"bodhisatvaofboogie" wrote:

I am creating a macro to do some pretty wacky filtering. Some of the ways
That I have it doing it is through pasting onto new workbooks. SO, after
I've reached the end result (which it seems to be working nicely) I have two
extra documents open and I wanted to incorporate into my macro to close them
automatically. I went ahead and recorded a macro of closing those two
documents, and this is what it looked like:

Windows("Book1").Activate
ActiveWindow.Close
Windows("Original Filename").Activate
ActiveWindow.Close

Which effectively leaves me on Book 2 with the finished product. SO, I just
want everything else to close. That above formula would work nicely on the
file it was recorded for, BUT, when I go to use this macro on new documents
and data, it won't work the same because the original filename will be
different. Any ideas??? Thanks!!!

The assumption is that you know the name of the workbook you want to keep
open.

Assume it is the workbook running the code:

' loop through all the workbooks that are open
for each bk in application.Workbooks
' restrict your actions to workbooks that are visible
' so you don't close personal.xls as an example
if bk.Windows(1).Visible then
msgbox bk.name
' Check if this workbook is not one you don't want to close
if bk.Name < ThisWorkbook.Name then
' close the workbook
bk.close SaveChanges:=False
end if
end if
Next

If the workbook running the code is one that will be closed, then you would
have to close it after closing others - otherwise the macro will halt when
you close it:

Dim bk as Workbook, bk1 as Workbook
set bk1 = Workbooks("book to remain open")
loop through all the workbooks that are open
for each bk in application.Workbooks
' restrict your actions to workbooks that are visible
' so you don't close personal.xls as an example
if bk.Windows(1).Visible then
msgbox bk.name
' Check if this workbook is not one you don't want to close
if bk.Name < ThisWorkbook.Name and _
bk.Name < bk1.Name then
' close the workbook
bk.close SaveChanges:=False
end if
end if
Next
thisworkbook.Close SaveChanges:=False

--
Regards,
Tom Ogilvy



"bodhisatvaofboogie" wrote:

hmmm...it looks to be trying to work, but I can't seem to get it to do what
it is supposed to. Essentially nothing is happening though. No errors are
popping up. Maybe I did something wrong. Break it down simply for me
please!!! :)

"Tom Ogilvy" wrote:

for each bk in application.Workbooks
if bk.Windows(1).Visible then
if bk.Name < "Name of book not to close" then
bk.close SaveChanges:=False
end if
end if
Next

--
Regards,
Tom Ogilvy


"bodhisatvaofboogie" wrote:

I am creating a macro to do some pretty wacky filtering. Some of the ways
That I have it doing it is through pasting onto new workbooks. SO, after
I've reached the end result (which it seems to be working nicely) I have two
extra documents open and I wanted to incorporate into my macro to close them
automatically. I went ahead and recorded a macro of closing those two
documents, and this is what it looked like:

Windows("Book1").Activate
ActiveWindow.Close
Windows("Original Filename").Activate
ActiveWindow.Close

Which effectively leaves me on Book 2 with the finished product. SO, I just
want everything else to close. That above formula would work nicely on the
file it was recorded for, BUT, when I go to use this macro on new documents
and data, it won't work the same because the original filename will be
different. Any ideas??? Thanks!!!

What if the windows I want closed are NOT visible?
I think that may be the issue. the two extras were just for filtering, and
I dont' know if it is recognizing them as visible.

PERSONAL is closing down, the others are remianing open.

All in All there are four open, PERSONAL, Orginal Imported File name, Book1,
Book2. Book 2 is the keeper, and PERSONAL is where the macro is being run,
so I want to close down Book1, and the Original Imported File Name. But I am
thinkin that they are not visible, hence they are not closing. whaddya
think? :)



"Tom Ogilvy" wrote:

The assumption is that you know the name of the workbook you want to keep
open.

Assume it is the workbook running the code:

' loop through all the workbooks that are open
for each bk in application.Workbooks
' restrict your actions to workbooks that are visible
' so you don't close personal.xls as an example
if bk.Windows(1).Visible then
msgbox bk.name
' Check if this workbook is not one you don't want to close
if bk.Name < ThisWorkbook.Name then
' close the workbook
bk.close SaveChanges:=False
end if
end if
Next

If the workbook running the code is one that will be closed, then you would
have to close it after closing others - otherwise the macro will halt when
you close it:

Dim bk as Workbook, bk1 as Workbook
set bk1 = Workbooks("book to remain open")
loop through all the workbooks that are open
for each bk in application.Workbooks
' restrict your actions to workbooks that are visible
' so you don't close personal.xls as an example
if bk.Windows(1).Visible then
msgbox bk.name
' Check if this workbook is not one you don't want to close
if bk.Name < ThisWorkbook.Name and _
bk.Name < bk1.Name then
' close the workbook
bk.close SaveChanges:=False
end if
end if
Next
thisworkbook.Close SaveChanges:=False

--
Regards,
Tom Ogilvy



"bodhisatvaofboogie" wrote:

hmmm...it looks to be trying to work, but I can't seem to get it to do what
it is supposed to. Essentially nothing is happening though. No errors are
popping up. Maybe I did something wrong. Break it down simply for me
please!!! :)

"Tom Ogilvy" wrote:

for each bk in application.Workbooks
if bk.Windows(1).Visible then
if bk.Name < "Name of book not to close" then
bk.close SaveChanges:=False
end if
end if
Next

--
Regards,
Tom Ogilvy


"bodhisatvaofboogie" wrote:

I am creating a macro to do some pretty wacky filtering. Some of the ways
That I have it doing it is through pasting onto new workbooks. SO, after
I've reached the end result (which it seems to be working nicely) I have two
extra documents open and I wanted to incorporate into my macro to close them
automatically. I went ahead and recorded a macro of closing those two
documents, and this is what it looked like:

Windows("Book1").Activate
ActiveWindow.Close
Windows("Original Filename").Activate
ActiveWindow.Close

Which effectively leaves me on Book 2 with the finished product. SO, I just
want everything else to close. That above formula would work nicely on the
file it was recorded for, BUT, when I go to use this macro on new documents
and data, it won't work the same because the original filename will be
different. Any ideas??? Thanks!!!

what distinguished Book2 from the other books? Let's assume for illustration
purposes that it is the only workbook with just 1 sheet. (personal.xls may
also have only one sheet or it may have multiple sheets)

for each bk in Application.Workbooks
if bk.sheets.count 1 and lcase(bk.name) < _
"personal.xls" then
bk.close SaveChanges:=False
end if
Next

--
Regards,
Tom Ogilvy


"bodhisatvaofboogie" wrote:

What if the windows I want closed are NOT visible?
I think that may be the issue. the two extras were just for filtering, and
I dont' know if it is recognizing them as visible.

PERSONAL is closing down, the others are remianing open.

All in All there are four open, PERSONAL, Orginal Imported File name, Book1,
Book2. Book 2 is the keeper, and PERSONAL is where the macro is being run,
so I want to close down Book1, and the Original Imported File Name. But I am
thinkin that they are not visible, hence they are not closing. whaddya
think? :)



"Tom Ogilvy" wrote:

The assumption is that you know the name of the workbook you want to keep
open.

Assume it is the workbook running the code:

' loop through all the workbooks that are open
for each bk in application.Workbooks
' restrict your actions to workbooks that are visible
' so you don't close personal.xls as an example
if bk.Windows(1).Visible then
msgbox bk.name
' Check if this workbook is not one you don't want to close
if bk.Name < ThisWorkbook.Name then
' close the workbook
bk.close SaveChanges:=False
end if
end if
Next

If the workbook running the code is one that will be closed, then you would
have to close it after closing others - otherwise the macro will halt when
you close it:

Dim bk as Workbook, bk1 as Workbook
set bk1 = Workbooks("book to remain open")
loop through all the workbooks that are open
for each bk in application.Workbooks
' restrict your actions to workbooks that are visible
' so you don't close personal.xls as an example
if bk.Windows(1).Visible then
msgbox bk.name
' Check if this workbook is not one you don't want to close
if bk.Name < ThisWorkbook.Name and _
bk.Name < bk1.Name then
' close the workbook
bk.close SaveChanges:=False
end if
end if
Next
thisworkbook.Close SaveChanges:=False

--
Regards,
Tom Ogilvy



"bodhisatvaofboogie" wrote:

hmmm...it looks to be trying to work, but I can't seem to get it to do what
it is supposed to. Essentially nothing is happening though. No errors are
popping up. Maybe I did something wrong. Break it down simply for me
please!!! :)

"Tom Ogilvy" wrote:

for each bk in application.Workbooks
if bk.Windows(1).Visible then
if bk.Name < "Name of book not to close" then
bk.close SaveChanges:=False
end if
end if
Next

--
Regards,
Tom Ogilvy


"bodhisatvaofboogie" wrote:

I am creating a macro to do some pretty wacky filtering. Some of the ways
That I have it doing it is through pasting onto new workbooks. SO, after
I've reached the end result (which it seems to be working nicely) I have two
extra documents open and I wanted to incorporate into my macro to close them
automatically. I went ahead and recorded a macro of closing those two
documents, and this is what it looked like:

Windows("Book1").Activate
ActiveWindow.Close
Windows("Original Filename").Activate
ActiveWindow.Close

Which effectively leaves me on Book 2 with the finished product. SO, I just
want everything else to close. That above formula would work nicely on the
file it was recorded for, BUT, when I go to use this macro on new documents
and data, it won't work the same because the original filename will be
different. Any ideas??? Thanks!!!

The ThisWorkbook property returns the workbook of the currentl
executing code. So (assuming the macro is "Original Filename" o
whatever) change:

Windows("Original Filename").Activate
ActiveWindow.Close

to:

thisworkbook.close



Co

--
colofnatur
-----------------------------------------------------------------------
colofnature's Profile: http://www.excelforum.com/member.php...fo&userid=3435
View this thread: http://www.excelforum.com/showthread.php?threadid=54544