how to make sure the file is saved on a network
 

Hi,
I have created an excel addin which adds a toolbar after help menu bar.
It opens querydatabase.xls in the same directory where addin exists in
readonly mode and allows you to add, edit, save records.
The file is not shared and saved with a open and readonly password
every time it saves. due to the password nobody opens the file in their
excel window and the addin is used by 15+ staff at a time.
whenever a user wants to add a record or edit record he is given a
userform to add record/ edit record. (the file is opened in the
background in readonly mode and window is hidden to retrieve the
original record to edit the record)

when the usr presses save, the file is opened in exclusive mode and
changes are saved by going to correct row by seeking the correct record
in edit by match function on primary key and if new record then the
last row by xlup

the problem is if 2 staff press save at the same time then excel
sometimes create a file with hex name like A00H080 without extension
(doesnt goto err handler if fails to save with exact name). i dont know
why this happens and how can i make sure that the record is saved or
not.

the line for opening in the background is as follows
workbooks(querydb).open querydbfullpath ,,:true (readonly),readPWD

when saving
workbooks.open querydbfullpath ,,opnPWD,readPWD
if workbooks(querydb).readonly)
msgbox "file is locked by somebody cant save now try after 10
seconds" : exitsub........
endif
change or add a record
workbooks(querydb).saveas workbooks(querydb).fullpath
,,opnPWD,readPWD,exclusive... etc.
workbooks.open querydbfullpath, readonly

but it doesnt return any err msg if the file is not saved. (document
not saved)

I have made sure that the file opened is not in readonly mode after it
is opened with workbooks(querydb).readonly flag.
So if a file is opened in exclusive mode and there is plenty of place
on server and saved immediately why the record isn't saved sometime?

it was working intially ok when file size was below 400k but now gives
a bit problem.


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

how to make sure the file is saved on a network
 

I'm not sure if it's a file contention issue that's causing your problem.

The same kind of thing can happen with a single user working on a file.

When you save a file, excel will save it as a temporary file (8 characters, no
extension). Then if that save is successful, excel will delete the original and
rename this funny named file to the original's name.

You see the results when something interferes with this process.

A couple of things that get pointed at are antivirus software and network
problems (hardware or permissions).

Maybe you could disable the antivirus software (temporarily) to see if that
helped.

If it's not the antivirus software, did something on your network change
recently?

(if the problem went away by disabling the AV software, maybe a visit to its
site to see if there are any upgrades available???)

===
And I would have guessed that as soon as one person got exclusive control of the
workbook, no other user could do much to interfere with that (but that's a
guess).



kedarkulkarni wrote:

Hi,
I have created an excel addin which adds a toolbar after help menu bar.
It opens querydatabase.xls in the same directory where addin exists in
readonly mode and allows you to add, edit, save records.
The file is not shared and saved with a open and readonly password
every time it saves. due to the password nobody opens the file in their
excel window and the addin is used by 15+ staff at a time.
whenever a user wants to add a record or edit record he is given a
userform to add record/ edit record. (the file is opened in the
background in readonly mode and window is hidden to retrieve the
original record to edit the record)

when the usr presses save, the file is opened in exclusive mode and
changes are saved by going to correct row by seeking the correct record
in edit by match function on primary key and if new record then the
last row by xlup

the problem is if 2 staff press save at the same time then excel
sometimes create a file with hex name like A00H080 without extension
(doesnt goto err handler if fails to save with exact name). i dont know
why this happens and how can i make sure that the record is saved or
not.

the line for opening in the background is as follows
workbooks(querydb).open querydbfullpath ,,:true (readonly),readPWD

when saving
workbooks.open querydbfullpath ,,opnPWD,readPWD
if workbooks(querydb).readonly)
msgbox "file is locked by somebody cant save now try after 10
seconds" : exitsub........
endif
change or add a record
workbooks(querydb).saveas workbooks(querydb).fullpath
,,opnPWD,readPWD,exclusive... etc.
workbooks.open querydbfullpath, readonly

but it doesnt return any err msg if the file is not saved. (document
not saved)

I have made sure that the file opened is not in readonly mode after it
is opened with workbooks(querydb).readonly flag.
So if a file is opened in exclusive mode and there is plenty of place
on server and saved immediately why the record isn't saved sometime?

it was working intially ok when file size was below 400k but now gives
a bit problem.

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

--

Dave Peterson

how to make sure the file is saved on a network
 

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/

how to make sure the file is saved on a network
 

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