First, you may want to move to an application like Access that's designed for
multiple users updating records.

(And I'm still not sure it's the file contention causing your save errors.)

I don't have any workbooks that are accessed by that many people, so I'm just
guessing.

I wouldn't believe that person A opening a file in Readonly mode would have any
affect on person B opening it in read/write mode. (again, just a guess).

But you would want to make sure that the file were opened in read/write mode.
I'm not sure if I'd bother checking to see if the file is already open. I think
I'd just open it and check to see if its .readonly property is true.

If it is, then warning to try again.

(If you do check with the binary open technique, won't you still have to check
to see if the file were really opened in read/write mode? Somebody could have
grabbed the file in those intervening milliseconds.)

Another technique that might eliminate any problems (real or perceived??) with
people opening the file in readonly mode--have the routine that opens the file
in readonly mode copy it to their temp folder first and open it from there.

Then everyone opening the file (readonly only) will have their own copy. Since
it doesn't sound like your users care when if it's updated by someone else, it
might work ok.

You can use FSO to do this. (excel's copyfile won't work if the file is already
open by someone else).

While you're developing your macro, it'll be easier if you set a reference so
that you'll get VBE's intellisense:

Inside the VBE:
Tools|References
Check Microsoft Scripting Runtime


Option Explicit
Sub testmeEarlyBinding()

Dim FSO As Scripting.FileSystemObject
Dim ROwkbk As Workbook
Dim ROwkbkName As String
Dim RWwkbkname As String

RWwkbkname = "c:\my documents\excel\book1.xls"
ROwkbkName = Environ("temp") & "\mycopyoffile.xls"

Set FSO = New Scripting.FileSystemObject
FSO.CopyFile RWwkbkname, _
ROwkbkName, _
overwritefiles:=True

Set ROwkbk = Workbooks.Open(Filename:=ROwkbkName, ReadOnly:=True)

End Sub

But when you're ready to cutover, remove that reference and use late binding to
create the object). This'll mean you won't have to worry about version
differences:


Sub testmeLateBinding()

Dim FSO As Object 'Scripting.FileSystemObject
Dim ROwkbk As Workbook
Dim ROwkbkName As String
Dim RWwkbkname As String

RWwkbkname = "c:\my documents\excel\book1.xls"
ROwkbkName = Environ("temp") & "\mycopyoffile.xls"

' = New Scripting.FileSystemObject
Set FSO = CreateObject("Scripting.FileSystemObject")
FSO.CopyFile RWwkbkname, _
ROwkbkName, _
True

Set ROwkbk = Workbooks.Open(Filename:=ROwkbkName, ReadOnly:=True)

End Sub


The True included in .copyfile overwrites any existing file.

But you could always add a Kill to clean up before (and after!!).

(And I'd use your second suggested code if I were chosing.)

=====
But I'm still not positive it's the file contention causing trouble. (I may
have said that already <bg.)




kedarkulkarni wrote:

Hi,
Thanx for reply

The problem is not resolved yet but I tried some of the following and
can you please let me know if I am on right track.

Before opening the file in exclusive mode I checked if the file is
being read by someone or not by Binary Open method
eg
on error resume next
Open querydbfullpath read lock read,write as fname
if err0 then file is read by someone etc.....
exit sub
this reduced my network trafic by not closing the file and trying to
open the same file in exclusive mode and checking if the file is in
readonly mode or not.

(just a guess probably if a usr is opening the file in readonly mode
and another is trying to save the changes in file, now for 1st usr the
o.s. though opening the file in readonly mode try to lock the file so
that the file is completely opened in readonly mode by denying write to
the other usr???)

One more thing can u tell what is the difference between following 2
codes. which 1 should i use?
1. workbooks(querydb).saveas _ querydb.fullname,,,,readpwd,openpwd,
true (create backup)

workbooks(querydb).close false
workbooks.open _ querydb.fullname,,,,readpwd,openpwd, true (create
backup)

and again open the file in read only mode

2.workbooks(querydb).save
workbooks(querydb).close false
and again open the file in read only mode

will that at all make any difference?

Thanx...

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson