Excel VBA Macro

Check If Excel Workbook is Already Open or Not

Posted on the October 28th, 2008 under Cells and Range by Poer @ Excel VBA/Macro

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.

Related Entries

• How To Open Excel File Using Macro
• Changing Microsoft Excel Status Bar
• Automatically Run an Excel Macro
• Schedules to Refresh Data Automatically using VBA Macro
• Automatically Protect/UnProtect All Worksheets

External Resources

• Microsoft Excel 2003/2007 Video Tutorials

  Step-by-step video guide to mastering Charts, PivotTable, Data Analysis and Macro programming in Microsoft Excel in 5 hours.

• 101 Secrets of Microsoft Excel

  Discover 101 of Excels little-known secrets that have been hiding right under your nose.

Tagged with: excel vba macro, excel workbook