{"id":12178,"date":"2013-06-16T17:55:43","date_gmt":"2013-06-16T17:55:43","guid":{"rendered":"http:\/\/www.learnexcelmacro.com\/?p=3043"},"modified":"2022-08-12T11:17:10","modified_gmt":"2022-08-12T11:17:10","slug":"is-file-already-open","status":"publish","type":"post","link":"https:\/\/vmlogger.com\/excel\/2013\/06\/is-file-already-open\/","title":{"rendered":"Check if file is already open using excel VBA"},"content":{"rendered":"

Dear LEM Readers,
\n 
\nWhile 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()<\/strong>) which takes the complete path of that file (with file name) as an Input parameter and returns a Boolean status TRUE<\/strong> or FALSE<\/strong> as shown below:<\/p>\n

Technique used : To find if file is already Open <\/h1>\n

There is a specific error number which is thrown by windows whenever it finds that File your are trying to open.. is already open<\/i><\/strong>.
\nThere is no specific function to find out whether file is open or not.. rather you can find out by capturing the error number.
\n 
\nIn 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.
\n<\/p>\n

\r\n\r\nFunction IsFileOpen(fileFullName As String)\r\n    Dim FileNumber As Integer\r\n    Dim errorNum As Integer\r\n\r\n    On Error Resume Next\r\n    FileNumber = FreeFile()   ' Assign a free file number.\r\n    ' Attempt to open the file and lock it.\r\n    Open fileFullName For Input Lock Read As #FileNumber\r\n    Close FileNumber       ' Close the file.\r\n    errorNum = Err         ' Assign the Error Number which occured\r\n    On Error GoTo 0        ' Turn error checking on.\r\n    ' Now Check and see which error occurred and based\r\n    ' on that you can decide whether file is already\r\n    ' open\r\n    Select Case errorNum\r\n        ' No error occurred so ErroNum is Zero (0)\r\n        ' File is NOT already open by another user.\r\n        Case 0\r\n         IsFileOpen = False\r\n\r\n        ' Error number for "Permission Denied." is 70\r\n        ' File is already opened by another user.\r\n        Case 70\r\n            IsFileOpen = True\r\n\r\n        ' For any other Error occurred\r\n        Case Else\r\n            Error errorNum\r\n    End Select\r\n\r\nEnd Function\r\n\r\n<\/code><\/pre>\n
How to use the above function? <\/h1>\n
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()<\/strong> from anywhere in the VBA code. Output of this function is always Boolean type either TRUE or False.<\/p>\n
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.
\n <\/p>\n
 Usage 1. Calling from VBA code <\/h1>\n
\r\n\r\nSub Test()\r\n\r\n'   While passing the FileName you need\r\n'   to pass complete path of the file\r\n'   including the file name with extension\r\n\r\n    If IsFileOpen("C:\/....\/File1.xls") = True Then\r\n    \r\n        MsgBox ("File is already opened")\r\n    Else\r\n        MsgBox ("File is not opened yet")\r\n    \r\n    End If\r\n\r\nEnd Sub\r\n\r\n<\/code><\/pre>\n
<\/p>\n
 2. Using as an Excel Formula (UDF – User Defined Function) <\/h1>\n
This might be really useful custom function to check if file which your formula is looking for is open or not. <\/p>\n
You can use this as a formula as well. Refer the below picture:
\n
\n\"IsFileOpen<\/p>\n
[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\u00a0create our own functions, based on our own need. Such functions are called U<\/strong>ser D<\/strong>efined F<\/strong>unctions<\/i>” (UDF<\/strong>s) or Custom Function.<\/strong><\/a><\/p>\n
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 \ud83d\ude09<\/p>\n
[\/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]<\/p>\n<\/span>","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":45,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[2056,5204],"tags":[],"yoast_head":"\nCheck if file is already open using excel VBA - Let's excel in Excel<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/vmlogger.com\/excel\/2013\/06\/is-file-already-open\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Check if file is already open using excel VBA\" \/>\n<meta property=\"og:description\" content=\"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 […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/vmlogger.com\/excel\/2013\/06\/is-file-already-open\/\" \/>\n<meta property=\"og:site_name\" content=\"Let's excel in Excel\" \/>\n<meta property=\"article:publisher\" content=\"http:\/\/www.facebook.com\/vmlogger\" \/>\n<meta property=\"article:author\" content=\"http:\/\/www.facebook.com\/vmlogger\" \/>\n<meta property=\"article:published_time\" content=\"2013-06-16T17:55:43+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-08-12T11:17:10+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2013\/06\/IsFileOpen.bmp\" \/>\n<meta name=\"author\" content=\"Vishwamitra Mishra\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@https:\/\/www.twitter.com\/learnexcelmacro\" \/>\n<meta name=\"twitter:site\" content=\"@learnexcelmacro\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Vishwamitra Mishra\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2013\/06\/is-file-already-open\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2013\/06\/is-file-already-open\/\"},\"author\":{\"name\":\"Vishwamitra Mishra\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"headline\":\"Check if file is already open using excel VBA\",\"datePublished\":\"2013-06-16T17:55:43+00:00\",\"dateModified\":\"2022-08-12T11:17:10+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2013\/06\/is-file-already-open\/\"},\"wordCount\":671,\"commentCount\":6,\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"articleSection\":[\"User Defined Function\",\"VBA Programming\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/vmlogger.com\/excel\/2013\/06\/is-file-already-open\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2013\/06\/is-file-already-open\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/2013\/06\/is-file-already-open\/\",\"name\":\"Check if file is already open using excel VBA - Let's excel in Excel\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\"},\"datePublished\":\"2013-06-16T17:55:43+00:00\",\"dateModified\":\"2022-08-12T11:17:10+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2013\/06\/is-file-already-open\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/vmlogger.com\/excel\/2013\/06\/is-file-already-open\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2013\/06\/is-file-already-open\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/vmlogger.com\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"User Defined Function\",\"item\":\"https:\/\/vmlogger.com\/excel\/udf\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Check if file is already open using excel VBA\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\",\"url\":\"https:\/\/vmlogger.com\/excel\/\",\"name\":\"Let's excel in Excel\",\"description\":\"Let's share knowledge\",\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/vmlogger.com\/excel\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\",\"name\":\"Vishwamitra Mishra\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png\",\"contentUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png\",\"width\":528,\"height\":560,\"caption\":\"Vishwamitra Mishra\"},\"logo\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/\"},\"description\":\"My name is Vishwamitra Mishra. Friends Call me Vishwa. I hold a Bachelor\u2019s Degree in Computer Science from D.A.V.V. Indore & currently working as a Technical Lead having over 7 years of experience.\",\"sameAs\":[\"http:\/\/www.learnexcelmacro.com\",\"http:\/\/www.facebook.com\/vmlogger\",\"https:\/\/twitter.com\/https:\/\/www.twitter.com\/learnexcelmacro\",\"https:\/\/www.youtube.com\/c\/VMLogger\"],\"url\":\"https:\/\/vmlogger.com\/excel\/author\/vishwamitra\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Check if file is already open using excel VBA - Let's excel in Excel","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/vmlogger.com\/excel\/2013\/06\/is-file-already-open\/","og_locale":"en_US","og_type":"article","og_title":"Check if file is already open using excel VBA","og_description":"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 […]","og_url":"https:\/\/vmlogger.com\/excel\/2013\/06\/is-file-already-open\/","og_site_name":"Let's excel in Excel","article_publisher":"http:\/\/www.facebook.com\/vmlogger","article_author":"http:\/\/www.facebook.com\/vmlogger","article_published_time":"2013-06-16T17:55:43+00:00","article_modified_time":"2022-08-12T11:17:10+00:00","og_image":[{"url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2013\/06\/IsFileOpen.bmp"}],"author":"Vishwamitra Mishra","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/www.twitter.com\/learnexcelmacro","twitter_site":"@learnexcelmacro","twitter_misc":{"Written by":"Vishwamitra Mishra","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/vmlogger.com\/excel\/2013\/06\/is-file-already-open\/#article","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/2013\/06\/is-file-already-open\/"},"author":{"name":"Vishwamitra Mishra","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"headline":"Check if file is already open using excel VBA","datePublished":"2013-06-16T17:55:43+00:00","dateModified":"2022-08-12T11:17:10+00:00","mainEntityOfPage":{"@id":"https:\/\/vmlogger.com\/excel\/2013\/06\/is-file-already-open\/"},"wordCount":671,"commentCount":6,"publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"articleSection":["User Defined Function","VBA Programming"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/vmlogger.com\/excel\/2013\/06\/is-file-already-open\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/vmlogger.com\/excel\/2013\/06\/is-file-already-open\/","url":"https:\/\/vmlogger.com\/excel\/2013\/06\/is-file-already-open\/","name":"Check if file is already open using excel VBA - Let's excel in Excel","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/#website"},"datePublished":"2013-06-16T17:55:43+00:00","dateModified":"2022-08-12T11:17:10+00:00","breadcrumb":{"@id":"https:\/\/vmlogger.com\/excel\/2013\/06\/is-file-already-open\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/vmlogger.com\/excel\/2013\/06\/is-file-already-open\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/vmlogger.com\/excel\/2013\/06\/is-file-already-open\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/vmlogger.com\/excel\/"},{"@type":"ListItem","position":2,"name":"User Defined Function","item":"https:\/\/vmlogger.com\/excel\/udf\/"},{"@type":"ListItem","position":3,"name":"Check if file is already open using excel VBA"}]},{"@type":"WebSite","@id":"https:\/\/vmlogger.com\/excel\/#website","url":"https:\/\/vmlogger.com\/excel\/","name":"Let's excel in Excel","description":"Let's share knowledge","publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/vmlogger.com\/excel\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5","name":"Vishwamitra Mishra","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/","url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png","contentUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png","width":528,"height":560,"caption":"Vishwamitra Mishra"},"logo":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/"},"description":"My name is Vishwamitra Mishra. Friends Call me Vishwa. I hold a Bachelor\u2019s Degree in Computer Science from D.A.V.V. Indore & currently working as a Technical Lead having over 7 years of experience.","sameAs":["http:\/\/www.learnexcelmacro.com","http:\/\/www.facebook.com\/vmlogger","https:\/\/twitter.com\/https:\/\/www.twitter.com\/learnexcelmacro","https:\/\/www.youtube.com\/c\/VMLogger"],"url":"https:\/\/vmlogger.com\/excel\/author\/vishwamitra\/"}]}},"_links":{"self":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/12178"}],"collection":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/users\/45"}],"replies":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/comments?post=12178"}],"version-history":[{"count":0,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/12178\/revisions"}],"wp:attachment":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/media?parent=12178"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/categories?post=12178"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/tags?post=12178"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}