Check if File is open already

  • Hey guys,


    How would I check to see if a file is already open? I have a workbook that automatically opens another workbook and updates a sheet, saves then closes it. The problem is, if it is already open, it is not giving me the in use dialog, just opens a read-only version and carries through. So how could I check with code to see if it is open?


    Thanks ahead of time!

  • Re: Check if File is open already


    Well I tried it, but it does not work. I left the msg Box's to test, and it always says it is not open even though it is. In fact it doesn't even seem to be checking it because it returns too fast. The external Workbook is on the company server, so it takes a minute to even open it, so for some reason it is not looking.


    Any thoughts?

  • Re: Check if File is open already



    Now basically once I know this works, I will have it open the file if it is not open by anyone else, and if it is then it will give the msgbox, then end.

  • Re: Check if File is open already


    I think the problem is that this code is looking to see if it is open on my computer, I need to see if someone else has the file open. Right now, If I run the Workbook which is on the server, if someone is in it, it gives me a read-only prompt. But if I open it using code, it does not give the prompt, just opens it as read-only automatically. I need it to check the file and if Somebody has it open, then it will prompt to try later, and if it is available, then it will open it. Hope that makes sense.

  • Re: Check if File is open already


    Oh! I understand now. I misunderstood your question.


    There probably is a way to determine the file properties of the file once you've opened it (read only or not), wich would tell you if someone else has it open already. I'm afraid I do not know the code for that. Hopefully someone else here does. I'll see if I can find anything an dpost back if a) I find something and b) someone else hasn't already provided the answer.


    It's got to be doable, and I'll bet it's easy.

  • Re: Check if File is open already


    Thanks for the link!


    Here is the code I am trying,


    Now when I have this in my module, how do I call it into my code in my workbook?

  • Re: Check if File is open already


    It's a boolean function (i.e. returns TRUE or false) that requires you to pass a string variable i.e. the filename... so you would use it like so


    Code
    strBook = "z:\Master\Test\breakout.xls"
     
    If IsFileLocked(strBook) Then
        MsgBox "Workbook is open "
    Else
        MsgBox "It's not open"
    End If
    End Sub
  • Re: Check if File is open already


    Thanks a ton Will. I am getting an error that Highlights the strBook in brackets on this line:

    Code
    If IsFileLocked(strBook) Then


    It give the error:ByRef argiment type mismatch.


    Any thoughts?


    Thanks again for your help!


    edit:


    Nevermind, just noticed it needs to be in " ". Will let you know how it works!

  • Re: Check if File is open already


    Well, this code is not working either. It works properly, but it always shows it as not open. Again it seems to not even check because it returns th emessage box instantly. Here is the code I am using:




    And with this in my module:


    Any thoughts?

  • Re: Check if File is open already


    I have figured out my problem. I didn't declare the path properly:
    This code now works for me.


    Thanks everyone for your help! A great learning experience.

  • Re: Check if File is open already


    Sorry, should have highlighted this probably


    Quote from Will Riley

    It's a boolean function (i.e. returns TRUE or false) that requires you to pass a string variable i.e. the filename...


    However, it's probablyu better that you worked it out yourself too - well done :) It highlights the need to explicitly declare your variables ;)

  • Re: Check if File is open already


    This is very interesting. I reworked this to look for any file open in a directory by changing the file name to "*.xls", but is there a way to return the name or names of files that are open?

  • Re: Check if File is open already


    Yep.. I guess so..


    you could load all the xls files in the specified directory into an array - and then loop through the array & test & remove any that were not open (using the above method) from the array... your resulting array is all the files that are currently open..... which you could display in a messagebox, range, listbox etc...

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!