VBA Check for Open Workbook in Excel

VBA Check for Open Workbook in Excel. We have flexibility to open multiple workbooks at a time in MS office. While running macro we may want to check particular workbook is open or not. Let us see specified Workbook is open or not in the following tutorial.

Macro to verify for Open Workbook in Excel VBA

Let us see the following macro to verify Workbook is open or not using Dir Function in Excel VBA.

'VBA Verify for Open Workbook in Excel
Sub Check_Open_Workbook()
    
    'Variable declaration
    Dim oWorkbook As Workbook
    
    For Each oWorkbook In Workbooks
        If oWorkbook.Name = "Test_Workbook.xls" Then
            MsgBox "Specified Workbook is open.", vbInformation, "VBAF1 : Automation Made Easy"
        Else
            MsgBox "Specified Workbook is not open.", vbCritical, "VBAF1 : Automation Made Easy"
        End If
    Next
End Sub

Note: lets assume ‘Test_Workbook.xls’ Workbook is not available. It shows the following output on the screen.

Here is the output screenshot of above macro.

Instructions to use Macro

Here are the instructions to use above macro procedure in Visual basic editor.

  • Save Visual Basic Editor(VBE) by clicking Alt +F11
  • Go to code window by clicking F7
  • Copy above specified macro or procedure
  • Paste above copied code in code window
  • Run macro by clicking F5 or Run command
  • You can see output on the screen
  • Find above output screenshot of the specified procedure.

Related Workbook Object articles

You may also like the related Workbook Object articles.

VBA Workbook Object




Leave a Comment

Your email address will not be published. Required fields are marked *