Testing for Open Workbook

[dcanham]

Hi All,
I'm trying to test for an existing Excel instance with a specific workbook. Now I may have to deal with several instances of Excel open at the same time on the workstation. I would like a way in VBA to look through what excel instances are open see if the desired workbook has been opened in them. This isn't to be confused with having multiple workbooks open in the same instance. I have tried several different ways in vain, some of them iterated below. I would appreciate any work code examples explaining what an dumkoff I am.

...this never detects the open workbook

Function OpenBook(sBookName As String)
    Dim k As Integer
    
    'Checks the names of all open  books
    For k = 1 To Workbooks.Count
        If Workbooks(k).Name = sBookName Then
            ' activate already open workbook
            Workbooks(k).Activate
            Exit Function
        End If
    Next k
     
    ' open workbook
    RetVal = Shell(("EXCEL.EXE " + Chr(34) + sFileBasePath + sBookName + Chr(34)), 1)
'    Workbooks.Open (sFileBasePath + sBookName)
End Function

...this doesn't work either

Sub TryThis()
    Dim WIsOpen As Workbook
    
    On Error Resume Next
    Set WIsOpen = Workbooks("c:\sheets\Planners.xls")
    If WIsOpen Is Nothing Then
        MsgBox "I'm not Open"
    Else
        MsgBox "I'm already Open"
    End If
    On Error GoTo 0
End Sub

 

[GlennUK]

Instead of trying to look through other instances of Excel ( which I don't think is possible by the way ), why not test to see if Excel has allowed you write access or read-only access? Or are you testing a shared book?

 

[dcanham]

I would have to start-up another instance of Excel to test that....and I'm not sure of a way to have a function return that status as a parameter? The idea behind what I'm trying to do is test if an excel instance is open with the workbook I want. If "yes" than maximize it, if "no" then start a new instance with that workbook. Right now, it is just getting the test to work correctly that is giving me grief.

 

[GlennUK]

I would have to start-up another instance of Excel to test that....and I'm not sure of a way to have a function return that status as a parameter? The idea behind what I'm trying to do is test if an excel instance is open with the workbook I want. If "yes" than maximize it, if "no" then start a new instance with that workbook. Right now, it is just getting the test to work correctly that is giving me grief.

Can you explain where you'll be doing this testing? You are talking as if you are doing your testing outside of Excel.

 

[dcanham]

I was going to make a little user form with launch buttons for the different sheets used. Much time is spent going between different applications and different excel spreadsheets. When the button on the user form is pushed, an excel instance is checked for that is running the desired sheet, if found to be running it is maximixed, otherwise a new instance of excel is started with the desired workbook.