Excel 2003 SP2: Close a file that someone else has open

[herkiefan]

I have reached an new high in my "annoyance-level."

Is there a way to close a file that someone else has open?

I have a workbook that only a few people have access to i.e., been given the password to; and one of them (my boss, of course) consistently opens the file and leaves it open.

Unfortunately due to confidentiality, office politics, and other bs reasons, I can not access my boss' cpu to close the file. Plus, he thinks he needs full rights to edit the file...

Two questions:
1. How do I "remotely-ish" close the file?
2. Is there a way to add a "close if not being used for x seconds" macro to the file?

Yours truly,

"Going crazy"

“Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.”-Albert Einstein

 

[AnotherHiggins]

I just replied to your other thread (thread68-1460980).

I suggest against going for a macro-in-the-file approach anyway because the macro would have to constantly loop over and over the entire time the workbook is open. That would take up computer resources....

Unless, and I'm just thinking off the top of my head here, you set a timer for X minutes after the workbook is opened. Then when the timer "goes off", you could check for activity. If no activity, save and close workbook, else reset timer for another X minutes.

But I'm not sure how you would check to see if there has been activity. Saved? Sure. But I don't think there is a timestamp for someone just making a change to the file, much less "viewing" the file. So the upshot might be that the file closes while your boss is looking at it.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[FaneDuru]

Hi,
Does the file has already any VBA code?
If Yes, ok. If not, you must hide programmatically the sheets which your boss is interested in and show them only if he presses Macro Enable.
Put the next code in a module. It closes Excel after how many minutes of computer inactivity you wont. I mean your boss does not move the mouse or type something on keyboard:
Code:

Option Explicit
'API for timer
Public Declare Function SetTimer Lib "user32" ( _
                ByVal hwnd As Long, ByVal nIDEvent As Long, _
                ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
Public Declare Function KillTimer Lib "user32" ( _
                    ByVal hwnd As Long, ByVal nIDEvent As Long) As Long
'API for user inactivity detection
Private Declare Function GetLastInputInfo Lib "user32.dll" _
                        (ByRef plii As PLASTINPUTINFO) As Long
Private Declare Function GetTickCount Lib "kernel32" () As Long
Private Type PLASTINPUTINFO
    cbSize As Long: dwTime As Long
End Type
Private TimerID As Long, TimerSeconds As Long, x As Long
Private Const TimeVal = 5, TimeLimit = 10
Sub Auto_Open()
    TimerSeconds = TimeVal ' how often the timer runs (sec).
    TimerID = SetTimer(0&, 0&, TimerSeconds * 1000, AddressOf Proc_Timer)
End Sub
Sub Proc_Timer(ByVal hwnd As Long, ByVal nIDEvent As Long, ByVal uElapse _
                                        As Long, ByVal lpTimerFunc As Long)
 Dim plii As PLASTINPUTINFO
'Sitting up the size
    plii.cbSize = Len(plii)
'The time from the last action of user
    GetLastInputInfo plii
'The time passed from the last action
    If Int((GetTickCount - plii.dwTime) / 1000) >= TimeLimit Then _
            Debug.Print "Inactive 10 seconds": OpritTimer
End Sub
Sub OpritTimer()
       On Error Resume Next
       KillTimer 0, TimerID
       Debug.Print "Timer stopped": On Error GoTo 0: Oprit_Calculator
End Sub
Sub Oprit_Calculator()
  If ThisWorkbook.Saved Then
    ThisWorkbook.Close
  Else
    ThisWorkbook.Save: ThisWorkbook.Close
  End If
End Sub

I have set TimeVal = 5 (Timer interval) and TimeLimit = 10 (number of seconds of inactivity). You can play with them in order to find the most appropriate figures.
I wonted to close the file but Excel quits... Is somebody able to help me understand why?
Beside the challenge of solving the problem this way why don't you just share the file?

Fane Duru

 

[macropod]

There are two rules in this office:
1. The boss is always right.
2. If you think the boss is wrong, see rule 1.

Peremptorily closing the file could create more problems than it solves: What if the boss has made some unsaved edits - which the boss may or may not want to save and which may or may not be incomplete. What are you going to do?

Solution: ask your boss to close the file and get on with something else (or even take a break) while you're waiting. Remember, if the boss wants full access to the file, thus preventing you from getting on with your work, that's his/her prerogative.

Cheers

[MS MVP - Word]

 

[FaneDuru]

I also do not think is the best solution. I was suggesting sharing of the file... Even if you can save all open workbooks before closing. You can inform your boss about the next behavior of the file...
But I made the code for the sake of challenge. Can anyone help me in understanding why Excel quits instead of closing the file? Maybe the timer is not properly closed... Technically it is also necessary a piece of code on BeforeClose event in order to Kill timer... I would like to receive comments in order to technically improve the solution.

Fane Duru