Check Workbook name currently opens is correctly

[Len]

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

 

[ozgrid.com]

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

 

[OssieMac]

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

 

[Chip Pearson]

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

 

[Len]

Hi All,

Thanks for your reply and your codes

I try out all suggested codes and they work perfectly


Regards
Len