Activating / Closing workbook using part name and a wild character

Hi,

Is it possible to activate/close an open workbook by a macro by using just
the part name alongwith with a wild character? Like if the workbook name is
"NewBook_6_10" and I want to activate/close it by just using "New*". I
tried using it but I get an error message. Thanks in advance.

Dim bk as Workbook
for each bk in application.Workbooks
if bk.Name like "New*" then
bk.Activate
exit for
end if
Next

--
Regards,
Tom Ogilvy


"sharman" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> Is it possible to activate/close an open workbook by a macro by using
just
> the part name alongwith with a wild character? Like if the workbook name
is
> "NewBook_6_10" and I want to activate/close it by just using "New*". I
> tried using it but I get an error message. Thanks in advance.
>
>

Hi Tom,

Thanks for the quick response. Using your code, I can activate and close the
workbook using just the part name. Can I also kill it by just using the part
name?

"Tom Ogilvy" wrote:

> Dim bk as Workbook
> for each bk in application.Workbooks
> if bk.Name like "New*" then
> bk.Activate
> exit for
> end if
> Next
>
> --
> Regards,
> Tom Ogilvy
>
>
> "sharman" <[email protected]> wrote in message
> news:[email protected]...
> > Hi,
> >
> > Is it possible to activate/close an open workbook by a macro by using
> just
> > the part name alongwith with a wild character? Like if the workbook name
> is
> > "NewBook_6_10" and I want to activate/close it by just using "New*". I
> > tried using it but I get an error message. Thanks in advance.
> >
> >
>
>
>

Hi Sharman,

Try something like:


Sub TestMe()
Dim WB As Workbook

For Each WB In Application.Workbooks
If LCase(Left(WB.Name, 3)) = "new" Then
WB.Close (False)
End If
Next
End Sub



---
Regards,
Norman



"sharman" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> Is it possible to activate/close an open workbook by a macro by using
> just
> the part name alongwith with a wild character? Like if the workbook name
> is
> "NewBook_6_10" and I want to activate/close it by just using "New*". I
> tried using it but I get an error message. Thanks in advance.
>
>

No. But once you have identified it you have the whole name.

Dim bk as Workbook
for each bk in application.Workbooks
if bk.Name like "New*" then
bk.Activate
sName = bk.FullName
bk.Close SaveChanges:=False
kill sName
exit for
end if
Next

Using Kill with a wildcard kills everything that matches.

--
Regards,
Tom Ogilvy

"sharman" <[email protected]> wrote in message
news:[email protected]...
> Hi Tom,
>
> Thanks for the quick response. Using your code, I can activate and close
the
> workbook using just the part name. Can I also kill it by just using the
part
> name?
>
> "Tom Ogilvy" wrote:
>
> > Dim bk as Workbook
> > for each bk in application.Workbooks
> > if bk.Name like "New*" then
> > bk.Activate
> > exit for
> > end if
> > Next
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "sharman" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi,
> > >
> > > Is it possible to activate/close an open workbook by a macro by using
> > just
> > > the part name alongwith with a wild character? Like if the workbook
name
> > is
> > > "NewBook_6_10" and I want to activate/close it by just using "New*".
I
> > > tried using it but I get an error message. Thanks in advance.
> > >
> > >
> >
> >
> >

Thanks a lot.

"Norman Jones" wrote:

> Hi Sharman,
>
> Try something like:
>
>
> Sub TestMe()
> Dim WB As Workbook
>
> For Each WB In Application.Workbooks
> If LCase(Left(WB.Name, 3)) = "new" Then
> WB.Close (False)
> End If
> Next
> End Sub
>
>
>
> ---
> Regards,
> Norman
>
>
>
> "sharman" <[email protected]> wrote in message
> news:[email protected]...
> > Hi,
> >
> > Is it possible to activate/close an open workbook by a macro by using
> > just
> > the part name alongwith with a wild character? Like if the workbook name
> > is
> > "NewBook_6_10" and I want to activate/close it by just using "New*". I
> > tried using it but I get an error message. Thanks in advance.
> >
> >
>
>
>