Boot user - force closure of xls via VBA

[kfschaefer]

I currently have a database that has multiple spreadsheets linked to the mdb. How do I handle the issue of Multiple users at the same time or if a users forgets to close a linked xls and prevents the admin from running the nightly updates?

There is no particular security besides the network sercurity. Also, multiple users may be access the same xls at the same time.

Is there a way to kick out someone and force the xls to close?

Note: MSDE not an option - and my database is the backend to the various spreadsheets. My client does not actual open Access - I am the only user of Access and this process is run nightly - except stops if a users forgot to close the xls.

I also looked he example that verifies if Excel wkbook open forces to close, unfortunately, due to security issues, I believe this could be a problem - due to the fact I will not have admin writes to the server that will allow me to kick the user off.

I also tried;

Private Function IsFileAlreadyOpen(Filename As String) As Boolean
' Returns TRUE if the workbook is open
Dim xlapp As Excel.Application ' be sure to be explicit about your object definitions, it will speed up your code and make it easier to debug
Dim xlwk As Excel.Workbook

Set xlapp = GetObject(, "Excel.Application")

Debug.Print xlapp.Workbooks.Count
For Each xlwk In xlapp.Workbooks
Debug.Print xlwk.Name
Next xlwk
xlapp.Quit

End Function

this works great if the spreadsheet is on my machine - but the spreadsheets will be accessed via the server and I need to be able to closes them no matter who or where.

Thanks,

Karen
:banghead: :banghead: :banghead: :aw

[Justinlabenne]

You would proably need code that would need to be in the Excel Workbooks that would log an open time to a cell, and run a timer. The timer would reset when any changes are made. If the timer exceeds a given amount (10 minutes maybe?) a procedure would need to be run to shutdown Excel.

This is a lot easier with Access, I don't have any code on hand for use within Excel, but I have seen this done before.

[geekgirlau]

The following code needs to go in the "ThisWorkbook" module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime EarliestTime:=gdtm_Active, Procedure:="TestActive", Schedule:=False
End Sub

Private Sub Workbook_Open()
gdtm_Active = Now()

Application.OnTime Now + TimeValue("00:10:00"), "TestActive"
End Sub

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
gdtm_Active = Now()
End Sub

This code needs to go in a standard module:

Global gdtm_Active As Date
Sub TestActive()
Dim dtmNext As Date


dtmNext = Now() + TimeValue("00:10:00")

Application.OnTime dtmNext, "TestActive"

If DateDiff("n", gdtm_Active, Now()) > 10 Then
gdtm_Active = dtmNext
ActiveWorkbook.Close
End If
End Sub

[stanl]

This may not be useful for the xls issue, but it's free: Stan

http://www.snapfiles.com/reviews/HAL/hal.html