Check if file is already open using excel VBA

.

Dear LEM Readers,
 
While doing programming with VBA many a times it happens to open an existing file. Before opening that file, it is always a good idea to check if that file is already open or not. Therefore I have written a function (IsFileOpen()) which takes the complete path of that file (with file name) as an Input parameter and returns a Boolean status TRUE or FALSE as shown below:

Technique used : To find if file is already Open

There is a specific error number which is thrown by windows whenever it finds that File your are trying to open.. is already open.
There is no specific function to find out whether file is open or not.. rather you can find out by capturing the error number.
 
In this function first I will open the file as given in the input parameter and capture the error number. If we are trying to open and already opened file then the Number of the error occurred is 70. Based on this technique, you can find if that file is already opened.



Function IsFileOpen(fileFullName As String)
    Dim FileNumber As Integer
    Dim errorNum As Integer

    On Error Resume Next
    FileNumber = FreeFile()   ' Assign a free file number.
    ' Attempt to open the file and lock it.
    Open fileFullName For Input Lock Read As #FileNumber
    Close FileNumber       ' Close the file.
    errorNum = Err         ' Assign the Error Number which occured
    On Error GoTo 0        ' Turn error checking on.
    ' Now Check and see which error occurred and based
    ' on that you can decide whether file is already
    ' open
    Select Case errorNum
        ' No error occurred so ErroNum is Zero (0)
        ' File is NOT already open by another user.
        Case 0
         IsFileOpen = False

        ' Error number for "Permission Denied." is 70
        ' File is already opened by another user.
        Case 70
            IsFileOpen = True

        ' For any other Error occurred
        Case Else
            Error errorNum
    End Select

End Function

How to use the above function?

Copy and Paste the above code in any of the module in your excel VBA code window. Now in that workbook you can call the function IsFileOpen() from anywhere in the VBA code. Output of this function is always Boolean type either TRUE or False.

This function can be used as a Cell Formula as well. In this workbook you can use this function as an excel formula which will take the same input and return TRUE of file is open and FALSE if not.
 

Usage 1. Calling from VBA code



Sub Test()

'   While passing the FileName you need
'   to pass complete path of the file
'   including the file name with extension

    If IsFileOpen("C:/..../File1.xls") = True Then
    
        MsgBox ("File is already opened")
    Else
        MsgBox ("File is not opened yet")
    
    End If

End Sub

2. Using as an Excel Formula (UDF – User Defined Function)

This might be really useful custom function to check if file which your formula is looking for is open or not.

You can use this as a formula as well. Refer the below picture:

IsFileOpen Function

[fullwidth background_color=”” background_image=”” background_parallax=”none” enable_mobile=”no” parallax_speed=”0.3″ background_repeat=”no-repeat” background_position=”left top” video_url=”” video_aspect_ratio=”16:9″ video_webm=”” video_mp4=”” video_ogv=”” video_preview_image=”” overlay_color=”” overlay_opacity=”0.5″ video_mute=”yes” video_loop=”yes” fade=”no” border_size=”0px” border_color=”” border_style=”” padding_top=”20″ padding_bottom=”20″ padding_left=”” padding_right=”” hundred_percent=”no” equal_height_columns=”no” hide_on_mobile=”no” menu_anchor=”” class=”” id=””][one_full last=”yes” spacing=”yes” center_content=”no” hide_on_mobile=”no” background_color=”#f9f9e8″ background_image=”” background_repeat=”no-repeat” background_position=”left top” hover_type=”none” link=”” border_position=”all” border_size=”1px” border_color=”#f2ca78″ border_style=”solid” padding=”10px” margin_top=”” margin_bottom=”” animation_type=”0″ animation_direction=”down” animation_speed=”0.1″ animation_offset=”” class=”” id=””][title size=”2″ content_align=”left” style_type=”none” sep_color=”” margin_top=”” margin_bottom=”” class=”” id=””]What is User Defined Function (UDF) ?[/title][fusion_text]Microsoft Excel allows us to create our own functions, based on our own need. Such functions are called User Defined Functions” (UDFs) or Custom Function.

These functions behaves exactly like Excel’s inbuilt formulas. These are helpful in simplifying complicated calculations or string related stuffs. Sometimes, you might have felt, “I wish, Excel had a built-in formula for this calculation” !! No worry, you can create your own formula.. and through all the dirty code behind the bar 😉

[/fusion_text][/one_full][tagline_box backgroundcolor=”” shadow=”yes” shadowopacity=”0.7″ border=”1px” bordercolor=”” highlightposition=”top” content_alignment=”left” link=”https://vmlogger.com/excel/2014/05/how-to-create-custom-functions-in-excel/” linktarget=”_blank” modal=”” button_size=”” button_shape=”” button_type=”” buttoncolor=”” button=”4 Steps to Create UDFs” title=”Learn How to create your own Excel formula in 4 simple Steps” description=”Hearing about creating my own defined formula is exiting and feels like a rocket science but trust it's not. Learn here in 4 simple steps how to create your own Custom Function.” margin_top=”” margin_bottom=”” animation_type=”0″ animation_direction=”down” animation_speed=”0.1″ animation_offset=”” class=”” id=””][/tagline_box]

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

4 Comments

  1. Jeff

    I really appreciate you making this information available. It worked perfectly. I’ve been researching/trying for hours. Is there a way to check to see if a password protected file is aleady open? If so, would you be so kind as to provide the code?

    Thanks again for your time and effort!!!!

    Reply
  2. prakash Shah

    hi
    Is it possible to know, once the file get opens, it send a message or record log. Here file would be not be the current file ( This workbook) but other files on path.
    Thanks,
    Prakash

    Reply
  3. Mohammad Naveed

    Hi,

    Nice Code !!,

    this code will give information file is open or not, if I want to know who opened the file then how we will write a code, as I am trying a lot but I unable to sort.

    could you please let me know how to get this done.

    thank you !

    Reply
    • KTO

      I am not sure how your code is written, but to retrieve the one who opened a file, use: Environ(“UserName”)

      How I do this is with the hundreds of files that I have created is, have all of the files macro enabled. When someone opens the file, all they see is a splashscreen (built on one worksheet) that says, ‘You must enable macros for this file to work properly.’ Once they enable the macro, I have a button they can click which triggers the “Workbooks_Open” code in the ThisWorkbook. Part of the code, unhides the other worksheets, uses Range(“A1”)FormulaR1C1=Environ(“UserName”) which gives their Windows login. On close, have Workbook_BeforeClose(Cancel As Boolean) unhide the splashscreen, hide all other worksheets and Range(“A1”).clearcontents. Create a workbook that is linked to all your files. Refresh the links regularly. If the file has no user’s ID then you know it is likely not opened, or at least, not being used.

      Reply

Trackbacks/Pingbacks

  1. Learn Excel Macro How to create User Defined Function in Excel - [...] can read and try following User Defined functions created: UDF – Is File Open? UDF to Convert Currency to…
  2. How to create User Defined Functions in Excel - Welcome to LearnExcelMacro.com - […]    UDF To check if File is Open? […]

Submit a Comment

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest