Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Workbook name currently opens is correctly
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Workbook name currently opens is correctly
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Workbook name currently opens is correctly
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 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Workbook name currently opens is correctly
Hi All,
Thanks for your reply and your codes I try out all suggested codes and they work perfectly Regards Len |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How To Check If The User Has Macros Enabled When The Workbook Opens | Excel Programming | |||
when opening an Excel Workbook, another blank workbook also opens | Excel Discussion (Misc queries) | |||
How to add a password check when an excel workbook opens? | Excel Programming | |||
Personal workbook opens when Excel opens | Excel Discussion (Misc queries) | |||
Check no. of records have copied correctly | Excel Programming |