Seek open workbooks and close them.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

catlair - 19 Jul 2006 03:35 GMT

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:Me:Documents:Piroas:MyWorkBook.xls"
Windows("MyWorkBook.xls").Activate
End If

Any help will be most appreciate!

catlair

Reply to this Message

Bob Greenblatt - 19 Jul 2006 13:18 GMT

On 7/18/06 10:35 PM, in article
1153276522.029416.92210@h48g2000cwc.googlegroups.com, "catlair"

> Hi,
>
[quoted text clipped - 23 lines]
>
> 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

Signature

Bob Greenblatt [MVP], Macintosh
bobgreenblattATmsnDOTcom

Reply to this Message

catlair - 21 Jul 2006 10:08 GMT

Hi Bob,

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

~ catlair

> On 7/18/06 10:35 PM, in article
> 1153276522.029416.92210@h48g2000cwc.googlegroups.com, "catlair"
[quoted text clipped - 39 lines]
> fileopen=false
> End function

Reply to this Message

Bob Greenblatt - 21 Jul 2006 13:13 GMT

On 7/21/06 5:08 AM, in article
1153472885.853097.228860@s13g2000cwa.googlegroups.com, "catlair"

> Hi Bob,
>
[quoted text clipped - 46 lines]
>> 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

Signature

Bob Greenblatt [MVP], Macintosh
bobgreenblattATmsnDOTcom

Reply to this Message

catlair - 27 Jul 2006 11:11 GMT

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?

> On 7/21/06 5:08 AM, in article
> 1153472885.853097.228860@s13g2000cwa.googlegroups.com, "catlair"
[quoted text clipped - 65 lines]
> next
> End sub

Reply to this Message

Bob Greenblatt - 27 Jul 2006 12:34 GMT

On 7/27/06 6:11 AM, in article
1153995111.074126.140790@i42g2000cwa.googlegroups.com, "catlair"

> Hi Bob,
>
[quoted text clipped - 70 lines]
>> next
>> End sub

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

Signature

Bob Greenblatt [MVP], Macintosh
bobgreenblattATmsnDOTcom

Reply to this Message

catlair - 28 Jul 2006 03:57 GMT

Thanks, Bob! Works like a charm! :-)

> On 7/27/06 6:11 AM, in article
> 1153995111.074126.140790@i42g2000cwa.googlegroups.com, "catlair"
[quoted text clipped - 80 lines]
> Call the sub from the workbook open event. Or, if the sub is in a module,
> name the sub "Auto_Open".

Reply to this Message

Mac Forum / Applications / Excel / July 2006

Seek open workbooks and close them.