Is there a way to save and close a spreadsheet when there has been no
activity for a particular amount of time? We have a problem with several
users needing access to the same workbook.
Thanks

One way described he-

http://www.ozgrid.com/forum/showthread.php?t=32949

Mike

"Steve" wrote:

Is there a way to save and close a spreadsheet when there has been no
activity for a particular amount of time? We have a problem with several
users needing access to the same workbook.
Thanks

Steve,

The first thing might be to see of your network software has the timed shutdown you need.
Doing it with Excel will require a macro, and the user will have the option upon opening to
disable macros (presuming you have the macro security set high enough, as you should).

--
Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
-----------------------------------------------------------------------
"Steve" wrote in message
...
Is there a way to save and close a spreadsheet when there has been no
activity for a particular amount of time? We have a problem with several
users needing access to the same workbook.
Thanks

Thanks Mike
Appears to work just fine, after some playing as to where to put some of the
subroutines

"Mike H" wrote:

One way described he-

http://www.ozgrid.com/forum/showthread.php?t=32949

Mike

"Steve" wrote:

Is there a way to save and close a spreadsheet when there has been no
activity for a particular amount of time? We have a problem with several
users needing access to the same workbook.
Thanks

Is this a good idea?

Will your close routine save or cancel the outstanding changes? How would you
know if you'll cause the user hours and hours of work by saving something that
shouldn't be saved--or discarding something that should be saved?



Steve wrote:

Is there a way to save and close a spreadsheet when there has been no
activity for a particular amount of time? We have a problem with several
users needing access to the same workbook.
Thanks

--

Dave Peterson

Hi Dave
These are data input spreadsheets that will be used on several workstations.
This is an attempt to avoid the problem of a user leaving the spreadsheet
open and walking away or going to luch or home. I know the Excel is not the
best multi user environment but it is all we currently have. We analyze the
data later.

"Dave Peterson" wrote:

Is this a good idea?

Will your close routine save or cancel the outstanding changes? How would you
know if you'll cause the user hours and hours of work by saving something that
shouldn't be saved--or discarding something that should be saved?



Steve wrote:

Is there a way to save and close a spreadsheet when there has been no
activity for a particular amount of time? We have a problem with several
users needing access to the same workbook.
Thanks

--

Dave Peterson

I understand the problem. But I'd be worried that the solution was worse than
the problem.



Steve wrote:

Hi Dave
These are data input spreadsheets that will be used on several workstations.
This is an attempt to avoid the problem of a user leaving the spreadsheet
open and walking away or going to luch or home. I know the Excel is not the
best multi user environment but it is all we currently have. We analyze the
data later.

"Dave Peterson" wrote:

Is this a good idea?

Will your close routine save or cancel the outstanding changes? How would you
know if you'll cause the user hours and hours of work by saving something that
shouldn't be saved--or discarding something that should be saved?



Steve wrote:

Is there a way to save and close a spreadsheet when there has been no
activity for a particular amount of time? We have a problem with several
users needing access to the same workbook.
Thanks

--

Dave Peterson


--

Dave Peterson

An additional problem is if someone leaves the sheet in edit mode then I
believe that the macros will not run to close the workbook.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Dave Peterson" wrote in message
...
I understand the problem. But I'd be worried that the solution was worse
than
the problem.



Steve wrote:

Hi Dave
These are data input spreadsheets that will be used on several
workstations.
This is an attempt to avoid the problem of a user leaving the spreadsheet
open and walking away or going to luch or home. I know the Excel is not
the
best multi user environment but it is all we currently have. We analyze
the
data later.

"Dave Peterson" wrote:

Is this a good idea?

Will your close routine save or cancel the outstanding changes? How
would you
know if you'll cause the user hours and hours of work by saving
something that
shouldn't be saved--or discarding something that should be saved?



Steve wrote:

Is there a way to save and close a spreadsheet when there has been no
activity for a particular amount of time? We have a problem with
several
users needing access to the same workbook.
Thanks

--

Dave Peterson


--

Dave Peterson

Hi Sandy
We do not let the users get into Edit mode, but thanks for the caution.

"Steve" wrote:

Hi Dave
These are data input spreadsheets that will be used on several workstations.
This is an attempt to avoid the problem of a user leaving the spreadsheet
open and walking away or going to luch or home. I know the Excel is not the
best multi user environment but it is all we currently have. We analyze the
data later.

"Dave Peterson" wrote:

Is this a good idea?

Will your close routine save or cancel the outstanding changes? How would you
know if you'll cause the user hours and hours of work by saving something that
shouldn't be saved--or discarding something that should be saved?



Steve wrote:

Is there a way to save and close a spreadsheet when there has been no
activity for a particular amount of time? We have a problem with several
users needing access to the same workbook.
Thanks

--

Dave Peterson

So you are saying that the workbook is protected and that nobody can write
anything in this workbook and just look up info? If not then the workbook
can definitely be in edit mode


--
Regards,

Peo Sjoblom



"Steve" wrote in message
...
Hi Sandy
We do not let the users get into Edit mode, but thanks for the caution.

"Steve" wrote:

Hi Dave
These are data input spreadsheets that will be used on several
workstations.
This is an attempt to avoid the problem of a user leaving the spreadsheet
open and walking away or going to luch or home. I know the Excel is not
the
best multi user environment but it is all we currently have. We analyze
the
data later.

"Dave Peterson" wrote:

Is this a good idea?

Will your close routine save or cancel the outstanding changes? How
would you
know if you'll cause the user hours and hours of work by saving
something that
shouldn't be saved--or discarding something that should be saved?



Steve wrote:

Is there a way to save and close a spreadsheet when there has been no
activity for a particular amount of time? We have a problem with
several
users needing access to the same workbook.
Thanks

--

Dave Peterson

Steve,

By "edit mode" I mean just be in the middle of entering something into a
cell. So if you click into a cell and enter: Steve
but don't press Enter, you will find that you cannot then run a Macro.

So if someone leaves that sheet like that the closing Macro will not run.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Steve" wrote in message
...
Hi Sandy
We do not let the users get into Edit mode, but thanks for the caution.

"Steve" wrote:

Hi Dave
These are data input spreadsheets that will be used on several
workstations.
This is an attempt to avoid the problem of a user leaving the spreadsheet
open and walking away or going to luch or home. I know the Excel is not
the
best multi user environment but it is all we currently have. We analyze
the
data later.

"Dave Peterson" wrote:

Is this a good idea?

Will your close routine save or cancel the outstanding changes? How
would you
know if you'll cause the user hours and hours of work by saving
something that
shouldn't be saved--or discarding something that should be saved?



Steve wrote:

Is there a way to save and close a spreadsheet when there has been no
activity for a particular amount of time? We have a problem with
several
users needing access to the same workbook.
Thanks

--

Dave Peterson

Gotcha
No way around that I guess. We'll just have to locate the offending computer
Thanks

"Sandy Mann" wrote:

Steve,

By "edit mode" I mean just be in the middle of entering something into a
cell. So if you click into a cell and enter: Steve
but don't press Enter, you will find that you cannot then run a Macro.

So if someone leaves that sheet like that the closing Macro will not run.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Steve" wrote in message
...
Hi Sandy
We do not let the users get into Edit mode, but thanks for the caution.

"Steve" wrote:

Hi Dave
These are data input spreadsheets that will be used on several
workstations.
This is an attempt to avoid the problem of a user leaving the spreadsheet
open and walking away or going to luch or home. I know the Excel is not
the
best multi user environment but it is all we currently have. We analyze
the
data later.

"Dave Peterson" wrote:

Is this a good idea?

Will your close routine save or cancel the outstanding changes? How
would you
know if you'll cause the user hours and hours of work by saving
something that
shouldn't be saved--or discarding something that should be saved?



Steve wrote:

Is there a way to save and close a spreadsheet when there has been no
activity for a particular amount of time? We have a problem with
several
users needing access to the same workbook.
Thanks

--

Dave Peterson

Maybe you could find an easier way to see who to yell at--create a log that
shows when the file is opened or closed. Then just open that log file (in a
readonly mode--notepad worked ok for me), look, close and yell.

If you tie up that log file, it may cause a similar problem that you're trying
to solve <vbg.

Option Explicit
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If lngX < 0 Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = ""
End If
End Function
Sub Auto_Open()

Dim FileNum As Long

If ThisWorkbook.ReadOnly = True Then
Exit Sub
End If

FileNum = FreeFile
Close #FileNum
'use a filename like c:\excel\book1.xls.txt
Open ThisWorkbook.FullName & ".txt" For Append As FileNum
Print #FileNum, Now, "OPEN: ", Application.UserName, fOSUserName, _
ThisWorkbook.FullName
Close #FileNum

End Sub
Sub Auto_close()

Dim FileNum As Long

If ThisWorkbook.ReadOnly = True Then
Exit Sub
End If

FileNum = FreeFile
Close #FileNum
Open ThisWorkbook.FullName & ".txt" For Append As FileNum
Print #FileNum, Now, "CLOSE: ", Application.UserName, fOSUserName, _
ThisWorkbook.FullName
Close #FileNum

End Sub

======
When I've done this kind of thing, I find that some users are using the wrong
workbook--they've saved a copy elsewhere. The log is a nice way to find out who
to yell at for that, too.

I've always just cleaned up the .txt file manually. But you could experiment a
bit.

Use "for output" in the auto_open procedure and "for append" in the auto_close
and you should only get a max of 2 records.

Use "for output" in both, and you'll only keep one record.

====
This assumes that users will always enable macros, too.


Steve wrote:

Gotcha
No way around that I guess. We'll just have to locate the offending computer
Thanks

"Sandy Mann" wrote:

Steve,

By "edit mode" I mean just be in the middle of entering something into a
cell. So if you click into a cell and enter: Steve
but don't press Enter, you will find that you cannot then run a Macro.

So if someone leaves that sheet like that the closing Macro will not run.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Steve" wrote in message
...
Hi Sandy
We do not let the users get into Edit mode, but thanks for the caution.

"Steve" wrote:

Hi Dave
These are data input spreadsheets that will be used on several
workstations.
This is an attempt to avoid the problem of a user leaving the spreadsheet
open and walking away or going to luch or home. I know the Excel is not
the
best multi user environment but it is all we currently have. We analyze
the
data later.

"Dave Peterson" wrote:

Is this a good idea?

Will your close routine save or cancel the outstanding changes? How
would you
know if you'll cause the user hours and hours of work by saving
something that
shouldn't be saved--or discarding something that should be saved?



Steve wrote:

Is there a way to save and close a spreadsheet when there has been no
activity for a particular amount of time? We have a problem with
several
users needing access to the same workbook.
Thanks

--

Dave Peterson






--

Dave Peterson

Very good idea Dave
Our manufacturing users have generic logon names for a workstation so it
still leaves a question of whom to yell at. At least I would know which
workstation.
Thanks a bunch.

"Dave Peterson" wrote:

Maybe you could find an easier way to see who to yell at--create a log that
shows when the file is opened or closed. Then just open that log file (in a
readonly mode--notepad worked ok for me), look, close and yell.

If you tie up that log file, it may cause a similar problem that you're trying
to solve <vbg.

Option Explicit
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If lngX < 0 Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = ""
End If
End Function
Sub Auto_Open()

Dim FileNum As Long

If ThisWorkbook.ReadOnly = True Then
Exit Sub
End If

FileNum = FreeFile
Close #FileNum
'use a filename like c:\excel\book1.xls.txt
Open ThisWorkbook.FullName & ".txt" For Append As FileNum
Print #FileNum, Now, "OPEN: ", Application.UserName, fOSUserName, _
ThisWorkbook.FullName
Close #FileNum

End Sub
Sub Auto_close()

Dim FileNum As Long

If ThisWorkbook.ReadOnly = True Then
Exit Sub
End If

FileNum = FreeFile
Close #FileNum
Open ThisWorkbook.FullName & ".txt" For Append As FileNum
Print #FileNum, Now, "CLOSE: ", Application.UserName, fOSUserName, _
ThisWorkbook.FullName
Close #FileNum

End Sub

======
When I've done this kind of thing, I find that some users are using the wrong
workbook--they've saved a copy elsewhere. The log is a nice way to find out who
to yell at for that, too.

I've always just cleaned up the .txt file manually. But you could experiment a
bit.

Use "for output" in the auto_open procedure and "for append" in the auto_close
and you should only get a max of 2 records.

Use "for output" in both, and you'll only keep one record.

====
This assumes that users will always enable macros, too.


Steve wrote:

Gotcha
No way around that I guess. We'll just have to locate the offending computer
Thanks

"Sandy Mann" wrote:

Steve,

By "edit mode" I mean just be in the middle of entering something into a
cell. So if you click into a cell and enter: Steve
but don't press Enter, you will find that you cannot then run a Macro.

So if someone leaves that sheet like that the closing Macro will not run.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Steve" wrote in message
...
Hi Sandy
We do not let the users get into Edit mode, but thanks for the caution.

"Steve" wrote:

Hi Dave
These are data input spreadsheets that will be used on several
workstations.
This is an attempt to avoid the problem of a user leaving the spreadsheet
open and walking away or going to luch or home. I know the Excel is not
the
best multi user environment but it is all we currently have. We analyze
the
data later.

"Dave Peterson" wrote:

Is this a good idea?

Will your close routine save or cancel the outstanding changes? How
would you
know if you'll cause the user hours and hours of work by saving
something that
shouldn't be saved--or discarding something that should be saved?



Steve wrote:

Is there a way to save and close a spreadsheet when there has been no
activity for a particular amount of time? We have a problem with
several
users needing access to the same workbook.
Thanks

--

Dave Peterson






--

Dave Peterson