Determin if a workbook is already opened by another user.

[BKoetting]

I have a macro that updates several workbooks. I need to be able to determin if the workbook is already open. I'm looking for something like a Workbooks property that returns true or false value on the current state of the workbook.

 

[north19701]

Re: Determin if a workbook is already opened by another user

try this link.

http://www.erlandsendata.no/english/vba/folders/fileinuse.htm

 

[Joe4]

Re: Determin if a workbook is already opened by another user

I did this once. Here's my code. See if you can modify it to work for you:

'   Open Invoice File 
    MyInvoiceFile = "W:\USERS\INVOICE #2002.xls"
    Set MyWorkBook = Workbooks.Open(MyInvoiceFile)

'   Check to see if file is already open
    If MyWorkBook.ReadOnly Then
        ActiveWorkbook.Close
        MsgBox "Cannot update Invoice Log, someone currently using file.  Please try again later."
        Exit Sub
    End If

 

[BKoetting]

Re: Determin if a workbook is already opened by another user

Thanks for your help, the code worked perfectly.

 

[MPW]

Re: Determin if a workbook is already opened by another user

What is the method to find out who has the workbook open? I need to update a content log with over a 100 people who can access it. It would be handy to be able to know who to contact if the automatic update fails.

 

[thenapolitan]

Re: Determin if a workbook is already opened by another user

MPW, did you ever get an answer to your question? I have the same question!

 

[billritz]

Re: Determin if a workbook is already opened by another user

The ReadOnly method above works when a workbook is open, but also flags a closed readonly workbook, so take that into account if you use it. I'd still like to know if there's a way to return the user who has the workbook open to my script, so that I can send him an email or IM or something. I'm using Win 7 with office 2013.

 

[MPW]

Re: Determin if a workbook is already opened by another user

I could determine if a file is in use with this:

        Ret = IsWorkBookOpen(Wherwithal & "\" & wkbkname)    
        If Ret = True Then
            MsgBox "The " & wkbkname & " is open." & vbCrLf & _
            "Please update it later."
            Exit Sub
        End If

Hope it helps.

 

[Greg Truby]

Re: Determin if a workbook is already opened by another user

...

        Ret = IsWorkBookOpen(Wherwithal & "\" & wkbkname)

...

Stumbled across this thread. Just a quick note to anyone reading it — IsWorkbookOpen() is not a built-in function. The return value of the function would depend on the code within it. Most versions of the IsWorkbookOpen() function (or sometimes the function is named WorkbookIsOpen or WorkbookOpen) posted online are a variation of a function John Walkenbach published decades ago and only check to see if a workbook is open within the active instance of Excel for the user running the macro.

@ billritz - if there is a way to ascertain the username of the person who has the workbook open using built-in properties of Excel or VBA, I've not seen it. One could create something in Excel, but it would not be native and would require a fair amount of work.

 

[billritz]

Re: Determin if a workbook is already opened by another user

Thanks for the reply. I'm using the .readonly to see if it's open, but I like you never did find a way of getting the name that is put in under options to let the user know who the user is. We had some people who'd open a workbook and cover it with other windows and forget it was open.