Ensure workbook.open doesnt open a copy if workbook is open

[Mlbblue]

This is a bit of a difficult one (well for me anyway, which is obvious as I'm looking for help!)

I've got an application that has a button on a user form that does the following:

1. - Opens a specific workbook on a network drive
2. - Gets the next reference and deletes the old reference
3. - Saves and closes this workbook and finally enters this ref into a field on the user form

Copies of this application will be given to more than one user (this is because it also submits data from the user form via an ADO connection to a .mdb file).

But, during some testing today I've found a problem. When two users hit this button to open the workbook that contains the next references at exactly the same time it's allowing them to both open the file (I assume one is opening a copy to get the last ref).

This means that references can potentially be duplicated.

My question is: is there some code that specifies that when the workbook containing the references is opened if it is already open then it throws out an error (either runtime or something I can use in an if statement)? I'm desperate to show that it's impossible for a reference duplication (which I thought I had). The code I used is below:

=================
Private Sub GetRef_Click()

On Error GoTo system_busy

Workbooks.Open Filename:= _
"C:\Pen_ref.xls"

On Error GoTo 0

Range("A1").Select
Selection.AutoFill Destination:=Range("A1:A2"), Type:=xlFillDefault
Range("A1:A2").Select
Rows("1:1").Select
Selection.Delete Shift:=xlUp
REF = Range("a1").Value

ActiveWorkbook.Save
ActiveWorkbook.Close

Exit Sub

system_busy: 'this is the on error routine

MsgBox "The system is busy at the moment, please try again"

End Sub
=============

Any help is appreciated!!!!

Cheers

Mark

 

[Cbrine]

I've never used this, but here it goes. One of the users is going to end up with a read-only copy open. Why not check the status and end the process if the read-only flag is set to true? Something along these lines.

Private Sub GetRef_Click() 

On Error GoTo system_busy 

Workbooks.Open Filename:= _ 
"C:\Pen_ref.xls" 

if activeworkbook.readonly = true then
  msgbox "File is currently open, please try again"
  activeworkbook.close false
  End
End if 

On Error GoTo 0 

Range("A1").Select 
Selection.AutoFill Destination:=Range("A1:A2"), Type:=xlFillDefault 
Range("A1:A2").Select 
Rows("1:1").Select 
Selection.Delete Shift:=xlUp 
REF = Range("a1").Value 

ActiveWorkbook.Save 
ActiveWorkbook.Close 

Exit Sub 

system_busy: 'this is the on error routine 

MsgBox "The system is busy at the moment, please try again" 

End Sub

HTH
Cal

 

[Mlbblue]

Thanks for this Cal, it's worked a treat!

Cheers

Mark