How do I check to see if a worksheet is already open in excel?

[Sasaraii]

I want to activate a specific worksheet at a certain time. However if that worksheet is not already open there will be an error.

What I want to do is check to see if that worksheet is open and if it isn't then I'll make the program open it.

If it is already open then i'll simply activate it as planned.

Thanks

[RobDog888]

This should do it.

Dim oWB As Excel.Workbook
    Dim i As Integer
    Dim bFound As Boolean
    For i = 1 To Application.Workbooks.Count
        If Application.Workbooks(i).Name = "SomeWorkbookThatIAmLookingFor.xls" Then
            bFound = True
            Exit For
        End If
    Next
    If bFound = True Then
        MsgBox "Open alreaady"
        Set oWB = Application.Workbooks("SomeWorkbookThatIAmLookingFor.xls")
    Else
        MsgBox "Not Open"
        Set oWB = Application.Workbooks.Open("SomeWorkbookThatIAmLookingFor.xls")
    End If

[Sasaraii]

Hmm, it doesn't seem to be working either.

There is something not working correct in this part:

For i = 2 To Application.Workbooks.Count
            
                If Application.Workbooks(i).Name = "H:\rrusnak\Equipment Log.xls" Then
                    bFound = True
                    Exit For
                End If
                bFound = True
            Next

It isn't getting the true part even when the workbook is already open. But I don't really understand what that if statement is doing so I'm not sure how to solve it.


Also, even if I manually set bFound to true, it will go to the message "already open". However after that it just says subscript out of range. Not sure why. What I need to happen in that case is for the other workbook to just be activated.


For example, when I run this macro I'm in workbook A. It checks to see if workbook B is open. If workbook B is open, then it just needs to activate it. If it isn't open, then it needs to open it and have it active.

When bFound is false, I get the message "not open" and then it opens workbook B, but then it tries opening it again and causes an error.

[RobDog888]

Step through the code by pressing F8 checking the values in the workbooks collection. It works, just need to determine
whats going on. Why did you change it to start from item #2?

[Sasaraii]

Oh I was just trying something with the number 2 and forgot to change it back.

Anyway, I found where the first error is happening:

[Highlight=VB]
Next
If bFound = True Then
MsgBox "Open already"
Set oWB = Application.Workbooks("H:\rrusnak\Equipment Log.xls")
Else
MsgBox "Not Open"
Set oWB = Application.Workbooks.Open("H:\rrusnak\Equipment Log.xls")
End If

The bolded part is where it is happening. Not sure why though.


It says: Runtime error '9', subscript out of range

[RobDog888]

and then it opens workbook B, but then it tries opening it again and causes an error.

Is this the same error or new error?

Why, if its the same error, is it trying to open it twice? If its opened, is it in the same application instance or a separate one? You can
tell by going to the Window menu and seeing the opened workbooks in that instance. I dont believe it can detect opened workbooks
in other instances.

[Sasaraii]

Both applications are open in the same instance.

I don't really understand why its opening it a second time but it does. I think the reason is because it isn't getting a true value even when it is already open so it keeps trying.

hmm, well I'll post all the code in case you feel like looking at it.

By the way, what exactly does the Next statement do? I've never used that before.

Option Explicit
 
 
Sub Find2()
'
' Find2 Macro
' Macro recorded 06/23/2005 by
'
 
        
    Range("A5").Select
    
    
    Cells.Find(What:="F00", after:=activeCell, LookIn:=xlValues, Lookat:= _
    xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False).activate
    
    Dim Issue As String
    Dim Date1 As String
    
    Issue = Range("B3").Value
    Date1 = Range("B2").Value
 
    Do Until activeCell.Value = "F00----"
    
        If activeCell.Value = "F00" Then
            Cells.Find(What:="F00", after:=activeCell, LookIn:=xlValues, Lookat:= _
            xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).activate
        
        Else
            Dim carry As String
            carry = activeCell.Value
        
            Dim oWB As Excel.Workbook
            Dim i As Integer
            Dim bFound As Boolean
            
            For i = 1 To Application.Workbooks.Count
            
                If Application.Workbooks(i).Name = "H:\rrusnak\Equipment Log.xls" Then
                    bFound = True
                    Exit For
                End If
                bFound = True
            Next
            If bFound = True Then
                MsgBox "Open already"
                Set oWB = Application.Workbooks("H:\rrusnak\Equipment Log.xls")
            Else
                MsgBox "Not Open"
                Set oWB = Application.Workbooks.Open("H:\rrusnak\Equipment Log.xls")
            End If
            
            Dim finish As Boolean
            Dim index As Integer
            index = 1
            finish = False
            
            Do Until finish = True
            
                If index = 12 Then
                
                    Dim Msg, Style, Title, Help, Ctxt, Response, MyString
                    Msg = "YTG Stock #" + carry + " Not Found"    ' Define message.
                    Style = vbOKOnly + vbExclamation + vbDefaultButton1    ' Define buttons.
                    Title = "Stock Number Not Found"    ' Define title.
                    Help = "DEMO.HLP"    ' Define Help file.
                    Ctxt = 1000    ' Define topic
                    ' context.
                    ' Display message.
                    Response = MsgBox(Msg, Style, Title, Help, Ctxt)
                    
                    If Response = vbYes Then    ' User chose Yes.
                        MyString = "Yes"    ' Perform some action.
                    End If
 
                    finish = True
                Else
                
                    Worksheets(index).activate
                
                    Range("B4").Select
                
                    Do Until activeCell.Value = "" Or finish = True
                    
                        If activeCell.Value = carry Then
                            activeCell([1], [2]).Select
                            activeCell = Issue
                            activeCell([1], [2]).Select
                            activeCell = Date1
                            finish = True
                        Else
                            activeCell.Offset([1], [0]).activate
                        End If
        
                    Loop
                    index = index + 1
                End If
            Loop
            Windows("Material Reconciliation Slips.xls").activate
            
            Cells.Find(What:="F00", after:=activeCell, LookIn:=xlValues, Lookat:= _
            xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).activate
        
        End If
        
        
    
    Loop
    
Windows("Equipment Log.xls").activate
Worksheets(1).activate
Windows("Material Reconciliation Slips.xls").activate
 
End Sub