Check Workbook name currently opens is correctly
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" wrote in message
...
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
|