VBA to reattempt to open if file is read only
 

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.

VBA to reattempt to open if file is read only
 

I Assume i would use but never done so before

On Error Resume Next

"Jive" wrote:

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.

VBA to reattempt to open if file is read only
 

Untested, but try this

Do
Application.Wait Now + TimeSerial(0, 0, 1)
Set wb = Workbooks.Open(Filename:="J:\ProSys\DataBase\Proje cts
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" wrote in message
...
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.

VBA to reattempt to open if file is read only
 

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?

"Bob Phillips" wrote:

Untested, but try this

Do
Application.Wait Now + TimeSerial(0, 0, 1)
Set wb = Workbooks.Open(Filename:="J:\ProSys\DataBase\Proje cts
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" wrote in message
...
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.

VBA to reattempt to open if file is read only
 

I modified it to this

Do
Set wb = Workbooks.Open(Filename:="J:\ProSys\DataBase\Proje cts
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.

"Bob Phillips" wrote:

Untested, but try this

Do
Application.Wait Now + TimeSerial(0, 0, 1)
Set wb = Workbooks.Open(Filename:="J:\ProSys\DataBase\Proje cts
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" wrote in message
...
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.