Good morning

I have a workbook located on a network directory to which
many users have access.

I am writing additional code to update that wokbook from
time to time.

I want to be able to tell, before any action is taken, if
anyone already has the workbook open, either "read only"
or "read/write"

Is is possisble to do this....and if so can the user who
has it open "read/write" be identified.

The file is not shared.

Using Win2000 and Excel2000

Thanks and regards

Michael Bond

Hi Michael,

This is just an idea but please try something like this.
When a user open the workbook, output a text file to network directory via
VBA.
When a user close the workbook, kill that text file.

When you would like to update the file, check if the text file exist or not.

Regards,
Colo

"Miochael Bond" wrote in message
...
Good morning

I have a workbook located on a network directory to which
many users have access.

I am writing additional code to update that wokbook from
time to time.

I want to be able to tell, before any action is taken, if
anyone already has the workbook open, either "read only"
or "read/write"

Is is possisble to do this....and if so can the user who
has it open "read/write" be identified.

The file is not shared.

Using Win2000 and Excel2000

Thanks and regards

Michael Bond

Hi Michael

You could perhaps open it readonly with code and check its "WriteReservedBy"
property to identify the read-write user. Perhaps, because this works well
on some networks and not at all on other. Which is said to be caused by "how
the network is built", I know nothing about those things.

If macros are enabled by the users, I think I'd code something in the file
to write the username to a log file on open/close.

--
HTH. Best wishes Harald
Followup to newsgroup only please

"Miochael Bond" skrev i melding
...
Good morning

I have a workbook located on a network directory to which
many users have access.

I am writing additional code to update that wokbook from
time to time.

I want to be able to tell, before any action is taken, if
anyone already has the workbook open, either "read only"
or "read/write"

Is is possisble to do this....and if so can the user who
has it open "read/write" be identified.

The file is not shared.

Using Win2000 and Excel2000

Thanks and regards

Michael Bond

Good idea....that will work, thanks


-----Original Message-----
Hi Michael,

This is just an idea but please try something like this.
When a user open the workbook, output a text file to
network directory via
VBA.
When a user close the workbook, kill that text file.

When you would like to update the file, check if the text
file exist or not.

Regards,
Colo

"Miochael Bond" wrote in
message
...
Good morning

I have a workbook located on a network directory to
which
many users have access.

I am writing additional code to update that wokbook from
time to time.

I want to be able to tell, before any action is taken,
if
anyone already has the workbook open, either "read only"
or "read/write"

Is is possisble to do this....and if so can the user who
has it open "read/write" be identified.

The file is not shared.

Using Win2000 and Excel2000

Thanks and regards

Michael Bond


.

Harald, Colo

I've combined both solutions and now have a text file
which logs everyone's use of the file (useful for
historical checking on who is accessing the file), and a
separate log file which shows who the current "read/write"
user is (includes username, computer name and date/time
opened). The log file is killed when the main file is
closed. My update application checks for the existence of
the log file. If not there it opens the file and runs my
update. If it is there it starts another sub which loops
at time intervals checking for the existance of the
file...and pops up a message when the file becomes
free....if the file stays open too long I can now contact
the user and ask them to close it....your idea has proved
most useful...I'm glad I asked.

Thanks and regards

Michael Bond

-----Original Message-----
Good morning

I have a workbook located on a network directory to which
many users have access.

I am writing additional code to update that wokbook from
time to time.

I want to be able to tell, before any action is taken, if
anyone already has the workbook open, either "read only"
or "read/write"

Is is possisble to do this....and if so can the user who
has it open "read/write" be identified.

The file is not shared.

Using Win2000 and Excel2000

Thanks and regards

Michael Bond

.

That's a neat result, Micheal. Glad our ideas helped, and you deserve a beer after
completing this.

Best wishes Harald
Followup to newsgroup only please.

"Michael Bond" wrote in message
...
Harald, Colo

I've combined both solutions and (...) ....your idea has proved
most useful

Hi Michael,

You are welcom and glad it helps!

Kind Regards
Colo
/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
Colo of 'The Road of The Cell Masters' :)

URL:http://www.interq.or.jp/sun/puremis/...astersLink.htm


/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/

"Michael Bond" wrote in message
...
Harald, Colo

I've combined both solutions and now have a text file
which logs everyone's use of the file (useful for
historical checking on who is accessing the file), and a
separate log file which shows who the current "read/write"
user is (includes username, computer name and date/time
opened). The log file is killed when the main file is
closed. My update application checks for the existence of
the log file. If not there it opens the file and runs my
update. If it is there it starts another sub which loops
at time intervals checking for the existance of the
file...and pops up a message when the file becomes
free....if the file stays open too long I can now contact
the user and ask them to close it....your idea has proved
most useful...I'm glad I asked.

Thanks and regards

Michael Bond

-----Original Message-----
Good morning

I have a workbook located on a network directory to which
many users have access.

I am writing additional code to update that wokbook from
time to time.

I want to be able to tell, before any action is taken, if
anyone already has the workbook open, either "read only"
or "read/write"

Is is possisble to do this....and if so can the user who
has it open "read/write" be identified.

The file is not shared.

Using Win2000 and Excel2000

Thanks and regards

Michael Bond

.

if the file stays open too long I can now contact
the user and ask them to close it

What if the user closes it but another user detects it has been closed
so they open it and your text file is locked because you've got it
open...?

My point is, you could get the workbook to look for a particular
string in the text file. This command could instruct the workbook to
close itself. You could manually add the command, giving you more
control over who can have the workbook open.

Consider using a database rather than a text file so that it can be
accessed simultaneously by you and the workbook (i.e. to avoid file
locking) and you could add a password to prevent anyone else accessing
it. A one table Jet database would do.

--

"Michael Bond" wrote in message ...
Harald, Colo

I've combined both solutions and now have a text file
which logs everyone's use of the file (useful for
historical checking on who is accessing the file), and a
separate log file which shows who the current "read/write"
user is (includes username, computer name and date/time
opened). The log file is killed when the main file is
closed. My update application checks for the existence of
the log file. If not there it opens the file and runs my
update. If it is there it starts another sub which loops
at time intervals checking for the existance of the
file...and pops up a message when the file becomes
free....if the file stays open too long I can now contact
the user and ask them to close it....your idea has proved
most useful...I'm glad I asked.

Thanks and regards

Michael Bond

-----Original Message-----
Good morning

I have a workbook located on a network directory to which
many users have access.

I am writing additional code to update that wokbook from
time to time.

I want to be able to tell, before any action is taken, if
anyone already has the workbook open, either "read only"
or "read/write"

Is is possisble to do this....and if so can the user who
has it open "read/write" be identified.

The file is not shared.

Using Win2000 and Excel2000

Thanks and regards

Michael Bond

.

"onedaywhen" skrev i melding
m...
What if the user closes it but another user detects it has been closed
so they open it and your text file is locked because you've got it
open...?

Text files and similar (ini, html, ...) are not "open" or "locked" the way
Office files and databases are. Try opening a textfile in notepad, and the
either again in another notepad, or try moving / deleting it in windows
explorer.

--
HTH. Best wishes Harald
Followup to newsgroup only please

Fair point, you can't lock a text file. I guess by saying 'locked' I
was being too literal. I've had problems in the past with multiple
users having read/write access to .ini files (as you say, essentially
text files). The problem wasn't that the text file was locking, rather
that a user *didn't* have to ability to lock it. Whatever, using a
text file wouldn't scale well.

The solution was to switch to using a RDBMS, which is optimized for
mutli-user access.

--

"Harald Staff" wrote in message ...
"onedaywhen" skrev i melding
m...
What if the user closes it but another user detects it has been closed
so they open it and your text file is locked because you've got it
open...?

Text files and similar (ini, html, ...) are not "open" or "locked" the way
Office files and databases are. Try opening a textfile in notepad, and the
either again in another notepad, or try moving / deleting it in windows
explorer.

Greetings all! Been a while since this thread was updated, but thought
I'd see if anyone got any new information relating to this.

I'm using Excel 2002 and I have a similar issue. I need to append data
to a workbook then send out a notice saying that it's been updated.
Probablem is, sometimes people open it and leave it open overnight so in
the morning when the report tries to run and append the data, the file
is "in use" by another user.

I could do the logging mentioned above and various similar techniques,
but really all I'd like to do is identify who has the file open and send
myself a note saying "Bob has the file open, data not appended".

One of the early suggestions was to look at the "WriteReservedBy", but
that's listing my current name (not username on the network and not the
username or real name of the user who actively has the workbook open).

"WriteReserved" is set to 'false' also. So I assume that means that
whoever's listed in "WriteReservedBy" does NOT have the reservation.

When you open the workbook, it says "Bob is using this, notify when it's
free?" or something like that. So obviously the system knows what
username has it open. But where do I get that data? This should be
really simple, but I've been digging through a watch of "application"
and "Activeworkbook" and such and can't find the username of the user
who has it open currently.

Has anyone found any more information on where to find the username of
the user who currently has the workbook open without doing any of the
logging and such mentioned?

Thanks in advance for any info you can provide. This is driving me nuts
because I know it should be really simple.. the data's just hiding
somewhere.

Thanks and have a great day!

-TG

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

You open the workbook and it says "Bob is using this, notify when it's
free?" or something like that.
and when you immediately check the below it behaves as you describe?

"WriteReserved" is set to 'false' also. So I assume that means that
whoever's listed in "WriteReservedBy" does NOT have the reservation.

"WriteReservedBy", but that's listing my current name

I haven't seen anyone come up with anything better, but I might have missed
it.
--
Regards,
Tom Ogilvy


"Trevor Gryffyn" wrote in message
...
Greetings all! Been a while since this thread was updated, but thought
I'd see if anyone got any new information relating to this.

I'm using Excel 2002 and I have a similar issue. I need to append data
to a workbook then send out a notice saying that it's been updated.
Probablem is, sometimes people open it and leave it open overnight so in
the morning when the report tries to run and append the data, the file
is "in use" by another user.

I could do the logging mentioned above and various similar techniques,
but really all I'd like to do is identify who has the file open and send
myself a note saying "Bob has the file open, data not appended".

One of the early suggestions was to look at the "WriteReservedBy", but
that's listing my current name (not username on the network and not the
username or real name of the user who actively has the workbook open).

"WriteReserved" is set to 'false' also. So I assume that means that
whoever's listed in "WriteReservedBy" does NOT have the reservation.

When you open the workbook, it says "Bob is using this, notify when it's
free?" or something like that. So obviously the system knows what
username has it open. But where do I get that data? This should be
really simple, but I've been digging through a watch of "application"
and "Activeworkbook" and such and can't find the username of the user
who has it open currently.

Has anyone found any more information on where to find the username of
the user who currently has the workbook open without doing any of the
logging and such mentioned?

Thanks in advance for any info you can provide. This is driving me nuts
because I know it should be really simple.. the data's just hiding
somewhere.

Thanks and have a great day!

-TG

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Ivan F Moala posted this (I didn't try it):

Yes you can do this programtically.
Have a look here for how to determine if a workbook is Open and by Who.

http://www.xcelfiles.com/IsFileOpen.html


Trevor Gryffyn wrote:

Greetings all! Been a while since this thread was updated, but thought
I'd see if anyone got any new information relating to this.

I'm using Excel 2002 and I have a similar issue. I need to append data
to a workbook then send out a notice saying that it's been updated.
Probablem is, sometimes people open it and leave it open overnight so in
the morning when the report tries to run and append the data, the file
is "in use" by another user.

I could do the logging mentioned above and various similar techniques,
but really all I'd like to do is identify who has the file open and send
myself a note saying "Bob has the file open, data not appended".

One of the early suggestions was to look at the "WriteReservedBy", but
that's listing my current name (not username on the network and not the
username or real name of the user who actively has the workbook open).

"WriteReserved" is set to 'false' also. So I assume that means that
whoever's listed in "WriteReservedBy" does NOT have the reservation.

When you open the workbook, it says "Bob is using this, notify when it's
free?" or something like that. So obviously the system knows what
username has it open. But where do I get that data? This should be
really simple, but I've been digging through a watch of "application"
and "Activeworkbook" and such and can't find the username of the user
who has it open currently.

Has anyone found any more information on where to find the username of
the user who currently has the workbook open without doing any of the
logging and such mentioned?

Thanks in advance for any info you can provide. This is driving me nuts
because I know it should be really simple.. the data's just hiding
somewhere.

Thanks and have a great day!

-TG

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

--

Dave Peterson

Thanks Dave! (and Ivan by proxy). Someone on another message board
posted something similar, but it gave me the username with a "yn" at the
end of it for some reason.

Ivan's code works better, but in case anyone's interested in the other
article, here it is:

http://www.markrowlinson.co.uk/exceliswbopen.php

Thanks again!

-TG

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!