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.

Related Entries

  • How To Open Excel File Using Macro

    Imagine this condition; We are in the middle of doing something using Excel VBA macro, then we want the macro to automatically show the File Open dialog box to make us (user) able to choose another Excel file to be opened by Excel. Got the picture? Below is vba procedure that do exactly like that, show...

  • Changing Microsoft Excel Status Bar

    Maybe for some reason we want to change our Microsoft Excel Status Bar, then this simple vba function will do the work for us. The end result will look like this:Ok, to do it, put this following code in ThisWorkbook codePrivate Sub Workbook_Open() 'when workbook opened, change the status bar ...

  • Automatically Run an Excel Macro

    Auto run macro excel… Do you want to run an excel macros automatically when the file is open or close? Microsoft Excel provide us the opportunity to run a macro automatically every time we open or close the excel workbook file, using the event Workbook_Open and Workbook_BeforeClose. In the Microsoft Visual Basic Editor page, in the ThisWorkbook module, we simply...

  • Schedules to Refresh Data Automatically using VBA Macro

    I once asked by Joshuacht about how to make a macro running every 15 minutes. Let say because every 15 minutes we have to refresh or reload the data in the Workbook. Below is a sample of how we can do this in Microsoft Excel *just an outline*. The logic is simple, when we open the...

  • Automatically Protect/UnProtect All Worksheets

    Tonight I want to automatically protect and unprotect all Microsoft Excel Worksheets in my Workbook with password, instead of doing it manually, I put these following Excel VBA macro code in my Workbook.Public Sub ProtectAllSheets()Dim objSheet As Worksheet'Protecting all worksheets with password For Each...

Valuable Resources

If you found this page useful, please consider bookmark it using social media or add a link to this page.

  • Print
  • Digg
  • Sphinn
  • del.icio.us
  • Facebook
  • Mixx
  • Google Bookmarks
  • connotea
  • Diigo
  • email
  • Fark
  • Identi.ca
  • Live
  • MisterWong
  • MySpace
  • Netvibes
  • NewsVine
  • PDF
  • Ping.fm
  • Propeller
  • Reddit
  • Simpy
  • StumbleUpon
  • Technorati
  • Twitter
  • Wikio
  • Yahoo! Bookmarks

Incoming search engine terms: verified file open vba, ms excel 2003 macro open workbook, microsoft excel vba check to see if a workbook is open, make excel not able to open a file if there it is already open, macro if workbook is open then, isopen vba excel 2007, if workbook open run macro if not then open workbook, If workbook name = then, if workbook is open then code, if workbook is open close it vba, how to detect if Excel is active already, office 2003 run worksheet macro when workbook opens, office 2007 macro when workbook opens closes, open file if not open using vba, vba test if excel workbook is open

«
»