Activating / Closing workbook using part name and a wild character

[Guest]

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.

 

[Tom Ogilvy]

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

 

[Guest]

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?

 

[Norman Jones]

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

 

[Tom Ogilvy]

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.

 

[Guest]

Thanks a lot.

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