Open file already open by other user

[Alberto Ast]

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?

 

[Barb Reinhardt]

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

 

[Alberto Ast]

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?

 

[AB]

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):
Data>Import External Data>New 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.

 

[Alberto Ast]

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.

 

[Dave Peterson]

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

===========

 

[Dave Peterson]

I lost an "End If" in my pseudo code:

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!
End if '<----------Added
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]

Thanks Dave.... It is a long one.. I will customize and try it... question...
where does it go when I type

I lost an "End If" in my pseudo code:

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!
End if '<----------Added
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

 

[Dave Peterson]

On error goto 0
is an instruction to the compiler that I don't expect any more errors to occur.

It doesn't really go anywhere--it's just the syntax that VBA uses to indicate
that the developer wants the compiler to handle the next (unhandled) error.

Thanks Dave.... It is a long one.. I will customize and try it... question...
where does it go when I type

 

[Alberto Ast]

I get an error message

Compile error:
Sub or Funtion not defined

If isfileopen("S:\Deviation EP\Deviations AA\Deviation Request RevA4.xls") =
True Then
MsgBox "can't be opened in read/write mode, okcontinue still false"

the error is poining to "isfileopen"

On error goto 0
is an instruction to the compiler that I don't expect any more errors to occur.

It doesn't really go anywhere--it's just the syntax that VBA uses to indicate
that the developer wants the compiler to handle the next (unhandled) error.

 

[Dave Peterson]

The isfileopen function came from the message AB posted.
http://www.vbaexpress.com/kb/getarticle.php?kb_id=468

You'll need to add it to the bottom of the module.

 

[Alberto Ast]

AB, Dave,

Thanks, finaly make it work....
I have my file name on cell A2 so I had a string on the function

myFile = sheets("File").range("A2") & ".xls"
isfileopen(myFile)

but looks like it does not work so I changed it to

isfileopen(sheets("File").range("A2") & ".xls")

this was making your macro to fail...
but TG I finaly got it... thanks for all your support.

 

[Dave Peterson]

How did you declare myFile?

Did you use:
Dim myFile as String

That's the only thing that I could see that would make a difference.

But glad you got it working.

 

[Alberto Ast]

Public myFile as String

How did you declare myFile?

Did you use:
Dim myFile as String

That's the only thing that I could see that would make a difference.

But glad you got it working.

 

[Dave Peterson]

I don't see why either code would fail to work.

Public myFile as String

 

[Alberto Ast]

Important think is that it already works. I really appreciate your help.
This discussion groups are excelent... I remember long time ago (early 90s)
there used to be a 1-800 number where you call and somebody helped over the
phone but I used to ask for very simple excel functions.... with all this
macro complexity is better to be able to write the whole issue and some parts
of the program.
Thanks

 

[Dave Peterson]

I bet someday when you try it again, it'll work fine--which would cause me to
believe there was a typo going on <vbg>.

And I agree with you--the newsgroups (and the historical database of
questions/answers that google has) is very valuable.