Check If Excel Workbook is Already Open or Not

In my case, I work a lot with transferring data from current Excel Workbook to another Excel Workbook, and to be able to do that, of course I need to make sure whether the destination Workbook is already open or not.

The following excel vba function assigned to check whether a workbook we need is open or not.

Using Workbook name as an input parameter, the function will do a looping in Workbooks collection to check all opened Workbook name, if there is a Workbook with the same name with the Workbook that we looking for, then the function will return true, and false if otherwise.

Public Function CheckSourceAvailability(sWorkBook As String) _
  As Boolean
    Dim wb As Workbook, bResult As Boolean
    bResult = False
    For Each wb In Application.Workbooks
        If InStr(LCase(wb.Name), LCase(sWorkBook)) > 0 Then
            bResult = True
            Exit For
        End If
    Next wb
    CheckSourceAvailability = bResult
End Function

All information about opened Workbook was saved by Excel in a collection object called Workbooks, just like Worksheets collection used by Excel to save information about all the Worksheets available.

FIN.