detect workbooks open

[nisha]

any ideas how to detect whether any workbooks are open? So that if they are not, then have code to open it ?? any sample code..

 

[DCrake]

Here is a function that checks of a known file is open or not

Function IsXLBookOpen(strName As String) As Boolean
     
     'Function designed to test if a specific Excel
     'workbook is open or not.
     
    Dim i As Long, XLAppFx As Excel.Application, NotOpen As Boolean
     
     'Find/create an Excel instance
    On Error Resume Next
    Set XLAppFx = GetObject(, "Excel.Application")
    If Err.Number = 429 Then
        NotOpen = True
        Set XLAppFx = CreateObject("Excel.Application")
        Err.Clear
    End If
     
     'Loop through all open workbooks in such instance
    For i = XLAppFx.Workbooks.Count To 1 Step -1
        If XLAppFx.Workbooks(i).Name = strName Then Exit For
    Next i
     
     'Set all to False
    IsXLBookOpen = False
     
     'Perform check to see if name was found
    If i <> 0 Then IsXLBookOpen = True
     
     'Close if was closed
    If NotOpen Then XLAppFx.Quit
     
     'Release the instance
    Set XLAppFx = Nothing
     
End Function

David