check if another user has an xls file open?

[Swoop_GB]

I need to be able to check if another user has an excel workbook open (from a network drive) from within another workbook.

user 1 has workbook1 open and has a macro to open workbook2, paste some data in, save it and close it, I need to add code to the macro to say if workbook2 is already open by another user then wait a second and try again.

any ideas?

[AndreRet]

You could use something like this in your workbook -

On Error Goto IsClosed

  If Not Workbooks("blabla") Is Nothing Then
     MsgBox "Workbook is open"
     Exit Sub
  End If
  
IsClosed:   MsgBox "Workbook is not open."

Or, You can use a: For Each...Next loop to check the .Name property of all open workbooks. If you find the needed workbook then activate it (or whatever) if you can't find it: open it.

A function to check if a sheet exists in open workbook -

Sub test()
    'does "Sheet1" exist in Book2?
    MsgBox SheetExists(Workbooks("Book2"), "Sheet1")
    
End Sub


Function SheetExists(ByVal wkb As Workbook, sName As String) As Boolean
    On Error Resume Next
    SheetExists = Not wkb.Sheets(sName) Is Nothing
End Function

To copy a sheet over to another workbook -

Sub Macro1()
'
' Macro1 Macro
'

'
    Sheets("Sheet2").Select
    Sheets("Sheet2").Copy After:=Workbooks("Book2").Sheets(3)
End Sub

[Swoop_GB]

thanks for the help, I'll try it and see what happens.

Just to be clear, this detects another user on a separate workstation openning a worksheet stored on a shared drive on a server?

The other problem I've got is I can't hard code the path to the other worksheet, at the moment I have it stored in a cell (will be user input) and write to it like this:

Dim user As Integer
         Dim path As String
         Dim brit As Integer
         
Sheets("System").Select
         user = Range("D18").Value
         brit = Range("F18").Value
         brit = brit + 1
         Range("F18").Value = brit
         path = Range("D14").Value
                  
Workbooks.Open (path)
         Sheets("Sheet1").Select
         Cells(user, 5).Value = brit
         ActiveWorkbook.Save
         ActiveWindow.Close

[AndreRet]

Why don't you just make the worksheet available on a network. That way the path stays the same, much easier to interact with it this way.:)

[Swoop_GB]

Yeah it will be, the trouble is the path will be different on each different network the program gets put on. There are 17,000 different LAN environments that this bit of software could potentially be installed to.

[AndreRet]

Then I would suggest you get the user to save the "server" IP address to a database and call the workbook using the IP from the database.

Will you know how to do this?

[Swoop_GB]

Er....no.