I have asked some of it already and I thought I had the answer but not really.
I have a macro where I open a file that can be access by others....
If the file is already open by other user it asked me if I want to be
notified or cancel but I do not want it to do it...

I did try
on error resume next
open file...

but it actually tell me file is open by others so it does not resume next...

I need to open as read only without being told when it is already open by
others or open as write if not being used by others...

I did try the wb.ReadOnly but it will be useful only after I open it.... but
need to open without asking me anything.... can somebody help?

Have you tried something like this
Dim oWB as Excel.workbook
Set oWB = Workbooks.Open(FileName,ReadOnly=True)

--
HTH,

Barb Reinhardt



"Alberto Ast" wrote:

I have asked some of it already and I thought I had the answer but not really.
I have a macro where I open a file that can be access by others....
If the file is already open by other user it asked me if I want to be
notified or cancel but I do not want it to do it...

I did try
on error resume next
open file...

but it actually tell me file is open by others so it does not resume next...

I need to open as read only without being told when it is already open by
others or open as write if not being used by others...

I did try the wb.ReadOnly but it will be useful only after I open it.... but
need to open without asking me anything.... can somebody help?

Thanks Barb but actually I need to open it as read/write.

I have a master file where I access and save some information... there is
only one master file but there is another file that many users have and all
of them can be accesing the info so I open the file as read only but when you
want to save a new record I have to open it as read/write.. it just last few
seconds because I open it save it and close it but if two users happen to
access it to save at the same time one of them fail so while opening excel
will tell you the file is already open and ask if I want to cancel or to be
notify... actually I need to avoid this question and have the macro open as
read if already in use by another user.

Any idea?

"Barb Reinhardt" wrote:

Have you tried something like this
Dim oWB as Excel.workbook
Set oWB = Workbooks.Open(FileName,ReadOnly=True)

--
HTH,

Barb Reinhardt



"Alberto Ast" wrote:

I have asked some of it already and I thought I had the answer but not really.
I have a macro where I open a file that can be access by others....
If the file is already open by other user it asked me if I want to be
notified or cancel but I do not want it to do it...

I did try
on error resume next
open file...

but it actually tell me file is open by others so it does not resume next...

I need to open as read only without being told when it is already open by
others or open as write if not being used by others...

I did try the wb.ReadOnly but it will be useful only after I open it.... but
need to open without asking me anything.... can somebody help?

Without knowing more about the structure you're using you could use
this:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=468
to check if file is already open by someone before taking any action.

Having said that it's quite possible that you don't need to do that at
all - provided your Master file is normalized and data are stored in
fine tables and the only thing you want is to get the data from the
Master also into the other files - in that instance it might be much
better to use queries to pull the data from Master file into the other
file using MsQuery (natvie to excel):
DataImport External DataNew Database query
This eliminates the need to store the same data in multiple places.
But obviously it depends on what you really need to achieve but i use
the MsQuery extensively.


On Nov 25, 6:03*am, Alberto Ast
wrote:
Thanks Barb but actually I need to open it as read/write.

I have a master file where I access and save some information... there is
only one master file but there is another file that many users have and all
of them can be accesing the info so I open the file as read only but when you
want to save a new record I have to open it as read/write.. it just last few
seconds because I open it save it and close it but if two users happen to
access it to save at the same time one of them fail so while opening excel
will tell you the file is already open and ask if I want to cancel or to be
notify... actually I need to avoid this question and have the macro open as
read if already in use by another user.

Any idea?



"Barb Reinhardt" wrote:
Have you tried something like this
Dim oWB as Excel.workbook
Set oWB = Workbooks.Open(FileName,ReadOnly=True)

--
HTH,

Barb Reinhardt

"Alberto Ast" wrote:

I have asked some of it already and I thought I had the answer but not really.
I have a macro where I open a file that can be access by others....
If the file is already open by other user it asked me if I want to be
notified or cancel but I do not want it to do it...

I did try
on error resume next
open file...

but it actually tell me file is open by others so it does not resume next...

I need to open as read only without being told when it is already open by
others or open as write if not being used by others...

I did try the wb.ReadOnly but it will be useful only after I open it..... but
need to open without asking me anything.... can somebody help?- Hide quoted text -

- Show quoted text -

Thanks AB but I think this will work if the file is already open by myself...
what I need is a way to know when a file is already open by another user so I
will not be asked if I want to open as read only.

The data I pull is just to fill in a simple format so no query is needed...
but if I generate a new record I have to save it in the master file so if two
users try to save at the sme time is when I get into problems because one
user will open Ok but second user will open be told file is open but I do not
wnat to be ask... just open as read only or do not open at all.

"AB" wrote:

Without knowing more about the structure you're using you could use
this:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=468
to check if file is already open by someone before taking any action.

Having said that it's quite possible that you don't need to do that at
all - provided your Master file is normalized and data are stored in
fine tables and the only thing you want is to get the data from the
Master also into the other files - in that instance it might be much
better to use queries to pull the data from Master file into the other
file using MsQuery (natvie to excel):
DataImport External DataNew Database query
This eliminates the need to store the same data in multiple places.
But obviously it depends on what you really need to achieve but i use
the MsQuery extensively.


On Nov 25, 6:03 am, Alberto Ast
wrote:
Thanks Barb but actually I need to open it as read/write.

I have a master file where I access and save some information... there is
only one master file but there is another file that many users have and all
of them can be accesing the info so I open the file as read only but when you
want to save a new record I have to open it as read/write.. it just last few
seconds because I open it save it and close it but if two users happen to
access it to save at the same time one of them fail so while opening excel
will tell you the file is already open and ask if I want to cancel or to be
notify... actually I need to avoid this question and have the macro open as
read if already in use by another user.

Any idea?



"Barb Reinhardt" wrote:
Have you tried something like this
Dim oWB as Excel.workbook
Set oWB = Workbooks.Open(FileName,ReadOnly=True)

--
HTH,

Barb Reinhardt

"Alberto Ast" wrote:

I have asked some of it already and I thought I had the answer but not really.
I have a macro where I open a file that can be access by others....
If the file is already open by other user it asked me if I want to be
notified or cancel but I do not want it to do it...

I did try
on error resume next
open file...

but it actually tell me file is open by others so it does not resume next...

I need to open as read only without being told when it is already open by
others or open as write if not being used by others...

I did try the wb.ReadOnly but it will be useful only after I open it..... but
need to open without asking me anything.... can somebody help?- Hide quoted text -

- Show quoted text -

.

dim TestWkbk as workbook
dim OkToContinue as boolean

set testwkbk = nothing
on error resume next
set testwkbk = workbooks("somenameincludingextensionhere.xls")
on error goto 0

oktocontinue = false 'assume something bad!
if testwkbk is nothing then
use code to see if anyone else has it open exclusively
if isfileopen("C:\...\xxxx.xls") = true then
'can't be opened in read/write mode, okcontinue still false
else
'open the file
on error resume next
set testwkbk = workbooks.open(filename:="c:\...\xxxx.xls")
on error goto 0
if testwkbk is nothing then
'open failed, oktocontinue still false
msgbox "not open and couldn't be opened!"
else
'opened successfully in read/write mode
'toggle that setting!
oktocontinue = true
end if
end if
else
'check to see if there's a workbook with the same name
'but a different folder--can't open if there's already a workbook
'with that name already open
if testwkbk.path = "C:\yourpathhere" then
'it's the "real workbook!
if testwkbk.readonly = true then
'oktocontinue still must be false
else
oktocontinue = true 'toggle it!
else
msgbox "Close the workbook with the same name"
'oktocontinue still should be false
end if
end if

if oktocontinue = false then
msgbox "some warning????"
else
'do the work
end if

===========


Alberto Ast wrote:

Thanks AB but I think this will work if the file is already open by myself...
what I need is a way to know when a file is already open by another user so I
will not be asked if I want to open as read only.

The data I pull is just to fill in a simple format so no query is needed...
but if I generate a new record I have to save it in the master file so if two
users try to save at the sme time is when I get into problems because one
user will open Ok but second user will open be told file is open but I do not
wnat to be ask... just open as read only or do not open at all.

"AB" wrote:

Without knowing more about the structure you're using you could use
this:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=468
to check if file is already open by someone before taking any action.

Having said that it's quite possible that you don't need to do that at
all - provided your Master file is normalized and data are stored in
fine tables and the only thing you want is to get the data from the
Master also into the other files - in that instance it might be much
better to use queries to pull the data from Master file into the other
file using MsQuery (natvie to excel):
DataImport External DataNew Database query
This eliminates the need to store the same data in multiple places.
But obviously it depends on what you really need to achieve but i use
the MsQuery extensively.


On Nov 25, 6:03 am, Alberto Ast
wrote:
Thanks Barb but actually I need to open it as read/write.

I have a master file where I access and save some information... there is
only one master file but there is another file that many users have and all
of them can be accesing the info so I open the file as read only but when you
want to save a new record I have to open it as read/write.. it just last few
seconds because I open it save it and close it but if two users happen to
access it to save at the same time one of them fail so while opening excel
will tell you the file is already open and ask if I want to cancel or to be
notify... actually I need to avoid this question and have the macro open as
read if already in use by another user.

Any idea?



"Barb Reinhardt" wrote:
Have you tried something like this
Dim oWB as Excel.workbook
Set oWB = Workbooks.Open(FileName,ReadOnly=True)

--
HTH,

Barb Reinhardt

"Alberto Ast" wrote:

I have asked some of it already and I thought I had the answer but not really.
I have a macro where I open a file that can be access by others....
If the file is already open by other user it asked me if I want to be
notified or cancel but I do not want it to do it...

I did try
on error resume next
open file...

but it actually tell me file is open by others so it does not resume next...

I need to open as read only without being told when it is already open by
others or open as write if not being used by others...

I did try the wb.ReadOnly but it will be useful only after I open it..... but
need to open without asking me anything.... can somebody help?- Hide quoted text -

- Show quoted text -

.


--

Dave Peterson