Check Workbook name currently opens is correctly

L

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
 
O

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
 
O

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
 
C

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
 
L

Len

Hi All,

Thanks for your reply and your codes

I try out all suggested codes and they work perfectly


Regards
Len
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top