VBA Check if File is Open

VBA Check if File is Open

VBA Check if File is Open or not using Excel VBA. In general we automate reports using Excel VBA. When we are dealing with multiple workbooks. It is always better to check whether file is opened or not. After that we can use those files for anything like data analysis, preparing reports etc.

VBA Check if File is Open or not in Excel

Let us see an example to check if file is opened or not using Excel VBA. In the following example we are looping through all opened workbooks and checking specified file is opened or not.

'VBA Check if File is Open
Sub VBAF1_Check_if_File_is_Open()

    'Variable declaration
    Dim sWBName As String
    Dim oWorkbook As Workbook
    Dim bChk As Boolean
    
    'Define file Name
    sWBName = "test.xlsx"
    
    'Loop through all opened workbooks
    For Each oWorkbook In Workbooks
        If oWorkbook.Name = sWBName Then
            'If file is open
            MsgBox "File is opened.", vbInformation, "VBAF1"
            bChk = True
        End If
    Next
    
    'If file is not open
    If bChk = False Then MsgBox "File is not opened.", vbInformation, "VBAF1"
    
End Sub

Output: In the above example we have specified file name as “test.xlsx”. You can change as per your requirement. We have provided comments for set of instructions to make you understand. You can find following screenshot for your reference. It displays when file is opened.
VBA Check if File is Open

Instructions to use VBA Macro Code

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

  • Open 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 find above specified input and output screenshot for your reference and better understand.

Related Articles

You can also learn complete details like syntax, example and etc by clicking on the following buttons.

VBA File and Directory Functions in Excel Related Posts

Leave a Comment

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