VBA to reattempt to open if file is read only

[Jive]

As per title really

I am making a temporary database in excel which will also be a functional
model for one we are having made externally.

However because of the number of people using it at some point two people
are going to hit the update button at the same time and one of them will get
a read only box appear.

What i would like to do is insead of the 2nd user getting the error message,
for the VBA code to wait 1 second and retry, and repeat until the file opens
before continuing.


Application.ScreenUpdating = False
Application.DisplayAlerts = False
DesignManager = ActiveWorkbook.Name

Workbooks.Open Filename:="J:\ProSys\DataBase\Projects Master.xls"
' If error wait one second and retry
ProjectsMaster = ActiveWorkbook.Name

Any help would be apreciated.

 

[Jive]

I Assume i would use but never done so before

 

[Bob Phillips]

Untested, but try this

Do
Application.Wait Now + TimeSerial(0, 0, 1)
Set wb = Workbooks.Open(Filename:="J:\ProSys\DataBase\Projects
Master.xls")
If wb.ReadOnly Then MsgBox "Still in use"
Loop Until Not wb.ReadOnly


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Jive]

Thank you that works fine and the manual input on the message box prevents
overloading the network with file open requests.

Dont suppose you know how to clear the file now available box that appears
after the code completes?

 

[Jive]

I modified it to this

Do
Set wb = Workbooks.Open(Filename:="J:\ProSys\DataBase\Projects
Master.xls")
If wb.ReadOnly Then Application.Wait Now + TimeSerial(0, 0, 1)
Loop Until Not wb.ReadOnly

Just the workbook now available messagebox to clear now.
Thanks again.