Hi,

Codes below try to check every existing workbook's name currently
opened is correct and if not, it exit sub
After several attempts, it still not able to run and prompts "
Subscript out of range"

Sub test()
Dim y As Long, x As Variant
x = Array("AB.xls", "CD.xls", "EF.xls", "GH.xls")
For y = LBound(x) To UBound(x)
If Workbooks(x(y)).Name = "AB.xls" Then
MsgBox Workbooks(x(y)).Name & " not found "
Exit Sub
ElseIf Workbooks(x(y)).Name = "CD.xls" Then
MsgBox Workbooks(x(y)).Name & " not found "
Exit Sub
ElseIf Workbooks(x(y)).Name = "EF.xls" Then
MsgBox Workbooks(x(y)).Name & " not found "
Exit Sub
ElseIf Workbooks(x(y)).Name = "GH.xls" Then
MsgBox Workbooks(x(y)).Name & " not found "
Exit Sub
End If
Next y
End Sub

Any helps will be appreciated and thanks in advance

Regards
Len

An array starts at zero is no valid for a Workbook Index (start at one).

Sub CheckOpenWB()
Dim wbTest As Workbook

On Error Resume Next
Set wbTest = Workbooks("AB.xls")
If wbTest Is Nothing Then MsgBox "AB.xls is not open"
Set wbTest = Nothing
Set wbTest = Workbooks("CD.xls")
If wbTest Is Nothing Then MsgBox "CD.xls is not open"
Set wbTest = Nothing
Set wbTest = Workbooks("EF.xls")
If wbTest Is Nothing Then MsgBox "EF.xls is not open"

End Sub

"Len" <(E-Mail Removed)> wrote in message
news:994156a6-6592-45cb-8aff-(E-Mail Removed)...
> Hi,
>
> Codes below try to check every existing workbook's name currently
> opened is correct and if not, it exit sub
> After several attempts, it still not able to run and prompts "
> Subscript out of range"
>
> Sub test()
> Dim y As Long, x As Variant
> x = Array("AB.xls", "CD.xls", "EF.xls", "GH.xls")
> For y = LBound(x) To UBound(x)
> If Workbooks(x(y)).Name = "AB.xls" Then
> MsgBox Workbooks(x(y)).Name & " not found "
> Exit Sub
> ElseIf Workbooks(x(y)).Name = "CD.xls" Then
> MsgBox Workbooks(x(y)).Name & " not found "
> Exit Sub
> ElseIf Workbooks(x(y)).Name = "EF.xls" Then
> MsgBox Workbooks(x(y)).Name & " not found "
> Exit Sub
> ElseIf Workbooks(x(y)).Name = "GH.xls" Then
> MsgBox Workbooks(x(y)).Name & " not found "
> Exit Sub
> End If
> Next y
> End Sub
>
> Any helps will be appreciated and thanks in advance
>
> Regards
> Len

Hi Len,

The following code should achieve what you want.

Sub test()
Dim wb As Workbook
Dim y As Long, x As Variant
x = Array("AB.xls", "CD.xls", "EF.xls", "GH.xls")

For y = LBound(x) To UBound(x)
Set wb = Nothing 'Initialize to nothing
On Error Resume Next
Set wb = Workbooks(x(y))
On Error GoTo 0 'Resume error trapping ASAP
If wb Is Nothing Then
MsgBox "Workbook " & x(y) & " not found "
Exit Sub
End If
Next y

End Sub

--
Regards,

OssieMac


"Len" wrote:

> Hi,
>
> Codes below try to check every existing workbook's name currently
> opened is correct and if not, it exit sub
> After several attempts, it still not able to run and prompts "
> Subscript out of range"
>
> Sub test()
> Dim y As Long, x As Variant
> x = Array("AB.xls", "CD.xls", "EF.xls", "GH.xls")
> For y = LBound(x) To UBound(x)
> If Workbooks(x(y)).Name = "AB.xls" Then
> MsgBox Workbooks(x(y)).Name & " not found "
> Exit Sub
> ElseIf Workbooks(x(y)).Name = "CD.xls" Then
> MsgBox Workbooks(x(y)).Name & " not found "
> Exit Sub
> ElseIf Workbooks(x(y)).Name = "EF.xls" Then
> MsgBox Workbooks(x(y)).Name & " not found "
> Exit Sub
> ElseIf Workbooks(x(y)).Name = "GH.xls" Then
> MsgBox Workbooks(x(y)).Name & " not found "
> Exit Sub
> End If
> Next y
> End Sub
>
> Any helps will be appreciated and thanks in advance
>
> Regards
> Len
> .
>

Try something like

Dim Sh As Variant
Dim N As Long
Dim WB As Workbook
Sh = Array("Book2", "Book3", "Book99")
For N = LBound(Sh) To UBound(Sh)
On Error Resume Next
Set WB = Nothing
Set WB = Application.Workbooks(Sh(N))
If Err.Number = 0 Then
Debug.Print "book '" & WB.Name & "' is open."
Else
Debug.Print "book '" & Sh(N) & "' is not open."
End If
Next N



Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com



On Sun, 28 Mar 2010 20:03:37 -0700 (PDT), Len
<(E-Mail Removed)> wrote:

>Hi,
>
>Codes below try to check every existing workbook's name currently
>opened is correct and if not, it exit sub
>After several attempts, it still not able to run and prompts "
>Subscript out of range"
>
>Sub test()
>Dim y As Long, x As Variant
>x = Array("AB.xls", "CD.xls", "EF.xls", "GH.xls")
>For y = LBound(x) To UBound(x)
>If Workbooks(x(y)).Name = "AB.xls" Then
> MsgBox Workbooks(x(y)).Name & " not found "
>Exit Sub
>ElseIf Workbooks(x(y)).Name = "CD.xls" Then
>MsgBox Workbooks(x(y)).Name & " not found "
>Exit Sub
>ElseIf Workbooks(x(y)).Name = "EF.xls" Then
>MsgBox Workbooks(x(y)).Name & " not found "
>Exit Sub
>ElseIf Workbooks(x(y)).Name = "GH.xls" Then
>MsgBox Workbooks(x(y)).Name & " not found "
>Exit Sub
>End If
>Next y
>End Sub
>
>Any helps will be appreciated and thanks in advance
>
>Regards
>Len

Hi All,

Thanks for your reply and your codes

I try out all suggested codes and they work perfectly


Regards
Len