none
Check if an excel file is opened by another user

    Question

  • Using Visual Studio 2010 VB and Excel 2010.

    My program opens 4 excel spreadsheets updating info. I update one at a time and then close it before opening the next one.

    I want to add code to my program to test if the file is already open, before trying to open it. Occasionally someone will have one of the files opened and it causes a crash. If I test to ensure it is not open, then I can avoid this.

    If it is open I want the program to wait until I can ask the user to close it before continuing.

            objBookMCR = objBooks.Open(strMasterCodeFile)
    Any help much appreciated.

    Thursday, April 26, 2012 2:44 PM

All replies

  • Hi JovialJohn02,

    Welcome to the MSDN forum.

    Please have a look at this reference link: http://social.msdn.microsoft.com/forums/en-us/csharpgeneral/thread/5B12DFBE-F16D-4934-82D1-695CD1A3C3BC . Generally you can open this file in try..catch block and check the exception to decide if this file is open by other user.

    If I misunderstood anything, please feel free and let me know.

    Best regards,


    Shanks Zen
    MSDN Community Support | Feedback to us

    Friday, April 27, 2012 8:40 AM
  • Thanks Shanks,

    This is what I tried yesterday, the file was not opened by another user so it wasn't really a valid test. I'll run it today as below with the file opened on another machine and see what happens.

            Try
                objBookMCR = objBooks.Open(strMasterCodeFile)
            Catch ex As Exception
                If TypeOf ex Is System.IO.DirectoryNotFoundException Then
                    MsgBox("Directory Not Found caught")
                Else
                    MsgBox("Error Type To Catch Is:  " & ex.GetType.ToString)
                End If
            End Try

    I am not sure exactly what exception I should be looking for.

    Also I would like to detect the username of the person that has it open so I can call them and ask them to close it.

    If it detects an exception and pops up a message box does execution of the program halt? Can I call the person ask them to close it and click on the message box to close it and expect that it will try again? I assume I would have to insert another try...catch block to retry opening the file just not sure how to do that.

    Thank you,

    John

    Friday, April 27, 2012 10:35 AM
  • Hi John,

    When exception was caught, program will not break out and you can take actions for this exception. If a file is open by another use, you should get an error like “The document file name is locked for editing by another user. To open a read-only copy of his document, click...”. As for detecting file is open by which user, you may need this http://technet.microsoft.com/en-us/sysinternals/bb897552.aspx. I haven’t tried this for the limitation of environment.

    Hope this will be helpful.

    Best regards,


    Shanks Zen
    MSDN Community Support | Feedback to us

    Monday, April 30, 2012 7:20 AM
  • Thanks again Shanks,

    The issue of detecting which user has the file open is not as significant as knowing what to do once an exception has occurred.

    I ran the code above, with the file open on another machine. No exception was thrown and process completed, file saved and closed.

    However after closing the file on the other machine, no changes were made to the file, what am I doing wrong?

    Monday, April 30, 2012 4:27 PM
  • Hi John,

    Sorry that I think I’ve misunderstood this thread.  In a network shared environment, this file can be open by multiple users. Maybe try to rename this file you can detect if this file was open by others.

    However, it can be edited by multiple users by the collaborative editing feature in Excel 2010. Take a look at this article: http://blogs.office.com/b/microsoft-excel/archive/2010/01/21/collaborative-editing-using-excel-web-app.aspx

    How does it work?

    Getting collaborative editing to work is really easy. There’s no switch to turn on, no special setting to enable. Basically, if two or more people have “edit” permissions on a file, then those people will be able to edit the file at any point, regardless of who is already editing. So, really, the only step to enable collaboration it to set the proper permissions on the file for those you want to grant “edit” access to (since permissions work differently whether you’re talking about SharePoint 2010 or Windows Live SkyDrive or SharePoint Online, I’m not going to go into those details here). Once the permissions are in place, you are all set!

    If I misunderstood anything, please feel free and let me know.

    Best regards,


    Shanks Zen
    MSDN Community Support | Feedback to us


    Tuesday, May 01, 2012 8:54 AM
  • Hi Shanks,

    If I open a file on my machine and then go to another machine on the network and try to open the same file I get the message "Filename.xls is locked for editing. by JovialJohn02".

    However, when I ran my program and opened the file, although the file was open on another machine, there was no error and it passed through the Try...Catch without being "caught". It appeared to run properly but when I closed the program and looked at the file that was still open on the other machine, the file appeared unchanged.

    What/Where did it "save" to?

    Why was it not caught.

    The link you provided appears to refer to Excel Web App, so I don't think it applies.

    I use the following function in Excel VBA which works properly and does detect if the file is open.

    Public Function IsFileOpen(filename As String)
        Dim FileNum As Integer, errnum As Integer

        On Error Resume Next   ' Turn error checking off.
        FileNum = FreeFile()   ' Get a free file number.
        ' Attempt to open the file and lock it.
        Open filename For Input Lock Read As #FileNum
        Close FileNum          ' Close the file.
        errnum = Err           ' Save the error number that occurred.
        On Error GoTo 0        ' Turn error checking back on.

        ' Check to see which error occurred.
        Select Case errnum
        Case 0
            ' No error occurred. File is NOT already open by another user.
            IsFileOpen = False
        Case 70
            ' Error number for "Permission Denied. File is already opened by another user.
            IsFileOpen = True
        Case Else
            ' Another error occurred.
            Error errnum
        End Select
    End Function

    Do you know if this will port over to VB 2010?


    Tuesday, May 01, 2012 10:57 AM
  • Hi John,

    Thank you for your feedback. I'm not familiar to VBA actually. Maybe some members in VBA forum (http://social.msdn.microsoft.com/Forums/en-US/isvvba/threads) will help. And this article (http://msdn.microsoft.com/en-us/library/aa192490(v=office.11).aspx) about converting VBA to VB.NET may be helpful also.

    Best regards,


    Shanks Zen
    MSDN Community Support | Feedback to us


    Thursday, May 03, 2012 2:55 AM