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

Mlbblue

New Member
Joined
Oct 8, 2006
Messages
20
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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
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.

Code:
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
 

Forum statistics

Threads
1,136,696
Messages
5,677,257
Members
419,682
Latest member
M3one

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top