Excel warning

[bakerd]

We use Excel for our office sign in and sign out. This works very well for us until someone needs to sign out and needs to leave NOW, but some nimrod has opened the sign out sheet to view the status of others or to sign out themselves and walked away, got on the phone or is visiting with someone and has forgotten to save and close.
As system administrator I constantly have to go to the server's Share and Storage Management Console's "Manage Open Files" utility to force a close on the sign out sheet. I would like to know if there is a way that the Excel sign out sheet could automatically save and close after non-use of say 1 minute or less. Or maybe there is some way to have it so when another user needs to open the sign out and they receive the message that it is open and in use and locked by another user that this new user could somehow override and open or somehow close it on the other users PC. And if that isn't bad enough there are many times that the "in use by another user and locked message" says it is in use by a specific user when in fact it is in use by someone else. The system sometimes does not report correctly who truly has the file open. WHAT A MESS!! I have searched high and low for a cheap or reasonably priced sign in - sign out software, but so far have not had much success finding a good one that can be modified and is such that all users can have the program open simultaneously and have instant reporting of changes to employees' status.

 

[baitmaster]

you could have an Access database sitting in the background, and send data to/from it using some VBA

You'd collect data about staff ID, sign in time, sign out time, location (?) - anything else you'd want

 

[bakerd]

you could have an Access database sitting in the background, and send data to/from it using some VBA

You'd collect data about staff ID, sign in time, sign out time, location (?) - anything else you'd want

Thank you for your suggestion, but I have never used access and am not very comfortable using vba, although I am very comfortable using autolisp for autocad, so am somewhat intelligent about writing code.

 

[baitmaster]

not very comfortable using vba

No worries, that's why you're here

And as for Access, I'm not very good either, but I know how to pass data to it and from it. Others on this board are experts, so feel free to look in the Access section if you need to

I would do this as follows:
A simple "flat" Access data table, containing 4 columns of data as I listed them before. Saved as a .mdb file - can you do this? i.e. have you got Access etc?

Your timesheet set up as you want in Excel (how much data do you want to be visible, you can do it with none visible at all..?), then have data passed to the database instead of to a data table held in Excel

To pass the data, you would need VBA code, but I have the code you need, and similar code is found throughout the MrExcel archives

You need to decide if this is enough data, or anything else you want to collect.
You also need to say what you want the file to look like, and the level of data you want to retrieve from the database (if any), assuming everything else will be sent to it from your spreadsheet.
I'll throw some code together that shows you how the data is passed to/from your database...

 

[bakerd]

No worries, that's why you're here

And as for Access, I'm not very good either, but I know how to pass data to it and from it. Others on this board are experts, so feel free to look in the Access section if you need to

I would do this as follows:
A simple "flat" Access data table, containing 4 columns of data as I listed them before. Saved as a .mdb file - can you do this? i.e. have you got Access etc?

Your timesheet set up as you want in Excel (how much data do you want to be visible, you can do it with none visible at all..?), then have data passed to the database instead of to a data table held in Excel

To pass the data, you would need VBA code, but I have the code you need, and similar code is found throughout the MrExcel archives

You need to decide if this is enough data, or anything else you want to collect.
You also need to say what you want the file to look like, and the level of data you want to retrieve from the database (if any), assuming everything else will be sent to it from your spreadsheet.
I'll throw some code together that shows you how the data is passed to/from your database...

Thank you so much for your help. First of all the new Microsoft OEM that came on our new 64 bit Windows 7 PCs from Dell included Access, but with only a 30 day trial after which we would buy it. But in this office (I've been here 18 years) we never use Access. So this is the first hurdle - to justify spending during a tight economy to buy Access for a purpose I am yet unable to assure others will be a good solution to our Excel sheet issue. If I had it to tinker with and get it to work, then have a few others involved then possibly there would be a justification for purchase. I assume Microsoft does not have a trial download, might put Bill Gates in the poor house.

 

[mathchick]

have you considered making it a shared file so multiple folks can have edit access to it at the same time?

 

[Darren Bartrup]

You could always save Excel as a shared workbook, as long as people aren't typing in the same cell then all should be good.

Or if you go the Access route you can use the Access runtime files so you only have to buy Access once.

If no end-user customization is required (including report modifications), you can choose to distribute those Access 2007 solutions so that they run without requiring a full installation of Access 2007. To do so, you must package and distribute your application with the Access 2007 Runtime.

http://www.microsoft.com/en-gb/download/details.aspx?id=4438

 

[baitmaster]

Shared workbook will definitely experience problems on a system like this. You are relying on all Users, accross multiple sites, from following the rules correctly, and not overwriting someone else's changes with their own whenever there is a conflict - guaranteed to fail.

Plus you are dealing with something to do with Payroll, and maybe confidential data, so there may be legal considerations

PM me your email address, and I can forward a development copy of the database I'm playing with (its actually a .accdb file, not a .mdb file as previously stated), plus a spreadsheet which now contains the following code, along with a few named ranges, a Submit button, and a reference to the MS ActiveX objects 2.8 library:

Option Explicit

Public adConn As ADODB.Connection

Sub submitTimes()

' set database connections
connectionOpen

' declare SQL to get full database contents
Dim strSQL As String: strSQL = "select * from tblData;"

' create recordset and apply settings
Dim rsTimesheet As ADODB.Recordset
Set rsTimesheet = New ADODB.Recordset

With rsTimesheet
    .CursorLocation = adUseServer
    .Open strSQL, adConn, adOpenStatic, adLockPessimistic, adCmdText
    .AddNew
    
    '!ID is automatic
    !staffID = Range("staffID")
    !Location = Range("location")
    If Range("signStatus") = "in" Then !signin = CLng(Now())
    If Range("signStatus") = "out" Then !signout = CLng(Now())
    !UserName = Environ("UserName")
    
    .Update
    .Close
    
End With
connectionClose
End Sub
 
Sub connectionOpen()
Set adConn = New ADODB.Connection
With adConn
   .CursorLocation = adUseServer
   .ConnectionTimeout = 500
   .Provider = "Microsoft.ACE.OLEDB.12.0"
   .ConnectionString = "Data Source=" & strPathToDB & ";"
   .Open
   .CommandTimeout = 500
End With
End Sub

Sub connectionClose()
adConn.Close
Set adConn = Nothing
End Sub

Function strPathToDB() As String
strPathToDB = ThisWorkbook.Path & "\timesheetConcept.accdb"
End Function

 

[bakerd]

Shared workbook will definitely experience problems on a system like this. You are relying on all Users, accross multiple sites, from following the rules correctly, and not overwriting someone else's changes with their own whenever there is a conflict - guaranteed to fail.

Plus you are dealing with something to do with Payroll, and maybe confidential data, so there may be legal considerations

PM me your email address, and I can forward a development copy of the database I'm playing with (its actually a .accdb file, not a .mdb file as previously stated), plus a spreadsheet which now contains the following code, along with a few named ranges, a Submit button, and a reference to the MS ActiveX objects 2.8 library:

Option Explicit

Public adConn As ADODB.Connection

Sub submitTimes()

' set database connections
connectionOpen

' declare SQL to get full database contents
Dim strSQL As String: strSQL = "select * from tblData;"

' create recordset and apply settings
Dim rsTimesheet As ADODB.Recordset
Set rsTimesheet = New ADODB.Recordset

With rsTimesheet
    .CursorLocation = adUseServer
    .Open strSQL, adConn, adOpenStatic, adLockPessimistic, adCmdText
    .AddNew
    
    '!ID is automatic
    !staffID = Range("staffID")
    !Location = Range("location")
    If Range("signStatus") = "in" Then !signin = CLng(Now())
    If Range("signStatus") = "out" Then !signout = CLng(Now())
    !UserName = Environ("UserName")
    
    .Update
    .Close
    
End With
connectionClose
End Sub
 
Sub connectionOpen()
Set adConn = New ADODB.Connection
With adConn
   .CursorLocation = adUseServer
   .ConnectionTimeout = 500
   .Provider = "Microsoft.ACE.OLEDB.12.0"
   .ConnectionString = "Data Source=" & strPathToDB & ";"
   .Open
   .CommandTimeout = 500
End With
End Sub

Sub connectionClose()
adConn.Close
Set adConn = Nothing
End Sub

Function strPathToDB() As String
strPathToDB = ThisWorkbook.Path & "\timesheetConcept.accdb"
End Function

Thank you so much for your input, I was just in the process of sharing the workbook and was seeing how well we thought it was working, but I concur with your statement about multiple users and the guarantee to fail. I have taken the workbook off of shared status and am ready to move on to something else. Here is my email address:

 

[baitmaster]

files sent to your email, you probably want this removed from the site though, to avoid receiving spam...

A couple of code changes to any other reviewers:
1 - added "option compare text" to module header
2 - Use of CLng was incorrect, meant CDbl - times saved in Access as Double, to avoid problems with Excel dates in UK format changing to incorrect values in US format, when passed to Access

Should also update code to report data has been submitted, and prevent it being re-entered... But this is a start...