Seek open workbooks and close them.

[catlair]

Hi,

Am pretty new to this macro stuff and have no VBA knowledge. I have
created a macro that will run from one file dedicated to a set of
macros to pull out some data from a master file, run an autofilter,
create a new workbook and then paste the new data into the new
workbook.

What I would like to do is before the macro runs the autofilter verify
if the master file is open. If it is then use it. If not then open it.
I have tried the following (place it within the macro) but it doesn't
work:

If ("MyWorkBook.xls").Open = True Then
Cells.Select
Selection.autofilter
ActiveWindow.SmallScroll ToRight:=6
Selection.autofilter Field:=15, Criteria1:="Field"
Else
Workbooks.Open Filename:= _
"Macintosh:Users:Meocumentsiroas:MyWorkBook.xls"
Windows("MyWorkBook.xls").Activate
End If

Any help will be most appreciate!

catlair

 

[Bob Greenblatt]

Hi,

Am pretty new to this macro stuff and have no VBA knowledge. I have
created a macro that will run from one file dedicated to a set of
macros to pull out some data from a master file, run an autofilter,
create a new workbook and then paste the new data into the new
workbook.

What I would like to do is before the macro runs the autofilter verify
if the master file is open. If it is then use it. If not then open it.
I have tried the following (place it within the macro) but it doesn't
work:

If ("MyWorkBook.xls").Open = True Then
Cells.Select
Selection.autofilter
ActiveWindow.SmallScroll ToRight:=6
Selection.autofilter Field:=15, Criteria1:="Field"
Else
Workbooks.Open Filename:= _
"Macintosh:Users:Meocumentsiroas:MyWorkBook.xls"
Windows("MyWorkBook.xls").Activate
End If

Any help will be most appreciate!

catlair

The easiest way to see if a file is open is to run a little loop like:

Function fileOpen(sfile as string) as boolean
Dim xx as variant
for each xx in workbooks
if xx.name= sfile then
fileopen=true
exit function
end if
next
fileopen=false
End function

 

[catlair]

Hi Bob,

If I wish to close that file or all files except the one running the
macro what should I include?

~ catlair

 

[Bob Greenblatt]

Hi Bob,

If I wish to close that file or all files except the one running the
macro what should I include?

~ catlair

The easiest way to see if a file is open is to run a little loop like:

Function fileOpen(sfile as string) as boolean
Dim xx as variant
for each xx in workbooks
if xx.name= sfile then
fileopen=true
exit function
end if
next
fileopen=false
End function

The function above returns TRUE if the work is open, FALSE if not. So,
you'll have to use a statement in another sub to do that. To close a file:
If fileopen("test.xls") workbooks("test.xls").close

To close all files except this one:
Sub CloseThemAll()
Dim XX as variant
for each xx in workbooks
if xx.name<>thisworkbook.name then xx.close
next
End sub

 

[catlair]

Hi Bob,

Thanks! It works like a dream! How do I enable it to run every time I
open the workbook where this macro resides as a workbook module?

 

[Bob Greenblatt]

Hi Bob,

Thanks! It works like a dream! How do I enable it to run every time I
open the workbook where this macro resides as a workbook module?

The function above returns TRUE if the work is open, FALSE if not. So,
you'll have to use a statement in another sub to do that. To close a file:
If fileopen("test.xls") workbooks("test.xls").close

To close all files except this one:
Sub CloseThemAll()
Dim XX as variant
for each xx in workbooks
if xx.name<>thisworkbook.name then xx.close
next
End sub

Call the sub from the workbook open event. Or, if the sub is in a module,
name the sub "Autpen".

 

[catlair]

Thanks, Bob! Works like a charm!