Close file on network drive opened by another user 1

[wmbb]

More people are using a planning workbook in a mapped network drive.
When someone is using the file other users are getting a message "the file is locked for edditing by....."
Normally you contact the person to close the file and reopen the file again.

The problem occurs when the person who opened the file, left for home without closing the file.
Because he locked his PC no one can access the file.

Is there a way to close such a file on the network drive ?

 

[xlhelp]

There should be a temp file on the network that excel created. Most likely it will be xxxxxxx.tmp type of file. If you delete that, you should be able to access the original file. Might not be bad idea to delete all *.tmp and ~*.* files on the network drive.


Avoid Hangovers. Stay drunk.

 

[wmbb]

This temp file can't be deleted because the user has the original file still open.
What I need is a forced close of the file.

Maybe it isn't possible but that was the question.
Is there any solution ?

 

[xlhelp]

I have yet to come across a file that cannot be deleted. If you or anyone in your organization knows DOS commands, it takes only a few seconds to delete the file.
You really only need to know CD and DELETE commands.

Also, you can try to change the user name on another machine to that of the other user. You MAY be able to open and close the file that way.


Avoid Hangovers. Stay drunk.

 

[kjv1611]

If the file is locked by another user, it cannot be deleted.

What I've done in the past, if the user has locked their machine, and left the building, and we needed access to the file, I just kill the power to the machine, then turn it back on. That forcefully releases control, and THEN you can delete the file.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

For situations like this I create a Workbook with macros. When the workbook is closed, it unhides a splash sheet with a message, "Macros must be enables to view this workbook" and all other sheets are VeryHidden.

If the workbook si opened without enabling macros, only the splash sheet is visible. If macros are enabled, the appropriate sheets are mad visible and the splash is hidden.

There is also a timer that is reset anytime a selection is made on a sheet. When the times reaches a set duration, the workbook automatically saves and closes, placing the workbook back in the spalsh sheet mode.

3 - 5 minutes might be a reasonable timer limit.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[wmbb]

Hi Skip,

Thanks for thinking with me.

This is exactly what I did.
I've put a timer of 5 minutes of inactivity but this doesn't seem to work for some users.
They have enabled the macro's but the workbook doesn't close always automatically.
There is also a problem when 2 workbooks are opened with those ontime-commands.
This is the code I'm using:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Reset auto-close time (5 min of inactivity from now)
timerset
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
'Reset auto-close time (5 min of inactivity from now)
timerset
End Sub

Sub timerset()
On Error GoTo labelend
'If exist disable the running ontime sequence
If stoptime > 0 Then Application.OnTime stoptime, "macro.Save_n_Close", , False
Application.StatusBar = "Closing Time: " & Format(stoptime, "HH:MM:SS")
'Set new auto-close time (5 min from now)
stoptime = DateAdd("n", 5, Now)
'Activate the new ontime sequence
Application.OnTime stoptime, "macro.Save_n_Close"
labelend:
'Shows the closing time in statusbar
Application.StatusBar = "Closing Time: " & Format(stoptime, "HH:MM:SS")
End Sub

Sub Save_n_Close()
With ThisWorkbook
 .Save
 .Close
End With
End Sub

Do you see any problems using this code ?

Please note, for most users this code works but not for all.

 

[combo]

Maybe it would be easier to create an add-in and install it among users. The idea:
- you still use OnTime, but the procedure is in the add-in,
- the add-in contains a class module that handles some application events (SheetChange and SheetSelectionChange, as in your code, maybe WorkbookOpen and WorkbookDeactivate too),
- the add-in builds an array of open workbooks to trace (workbook + close time), possible triggers - custom document property, network drive (can be found from workbook's path, network drive can be tested with Scripting libtary too, scrrun.dll: workbook's path => drive => drivetype = Remote).
- every traced action rebuilds the array and sets the OnTime time to the earliest time in the array.

combo

 

[strongm]

Boy, if you are going that far why not just use the shared workbook feature? It has certain limitations, but may be appropriate

 

[wmbb]

Hi strongm,

Simple answer: because I didn't know of this feature !
I'll look at this feature and maybe it is just what I was looking for in the first place...

Thanks for pointing me in that direction.

 

[kjv1611]

Skip,

Thanks for sharing your overall idea. That's something to think about for sure. So far, by the time I get a project built, I end up (sometimes) later wishing I had something like that built-in - both Excel and Access projects alike.



"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[Rick998]

Another method - which we use regularly against locked PC's - is to use PSKILL to terminate the process on the remote PC that's holding the file open.

It's quick and easy... and in this case would save having to write any macros.

Hope this helps...

 

[kjv1611]

Rick998,

Do you have to have certain Windows Domain permissions to use the PSKILL application, or does it "just work"? Reason I'm asking is it seems you'd HAVE to have something like a domain-level admin login to close something on another machine. If it doesn't require that, then it sounds more like a possible security threat and would/should eventually be blocked by a Windows update.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[Rick998]

Hi kjv611,

It depends on your environment.

In our previous Win XP Pro domain environment 2nd-line Support and Service Desk staff were able to use PSKILL (and other PSTools) using the local Administrator credentials on remote PCs. I adapted an Autohotkey utility based on PsTools GUI so SD staff could just input the process and IP address of the remote PC.

Now we've just moved from Win XP Pro to Win 7 Pro we've realised we're no longer able to use PSKILL (nor PSEXEC)... but not because of the changes to client platform and server backend. Instead it's the result of a recent penetration test recommendation. Due to the recommendation, several tools from the PSTools suite are no longer allowed (by a TVD ePO restriction) to run across our networks. (As 2nd-line support, we currently have an appeal against this restriction.)

As a result, I don't know whether we would now need to use 'RunAs' with 'Domain Administrator' credentials to kill processes on remote PC's in our new Win 7 Pro/Windows Server 2012 R2 environment.

PSKILL was written by Mark Russinovich, formerly of Sysinternals and now a Microsoft 'Technical Fellow' after Microsoft bought-out Sysinternals. As a result, it's probably unlikely that PSKILL or other Sysinternals utilities would be blocked by any future MS Update. However, as the saying goes, 'never say never'.

I wasn't involved in our move from TVD 5.5 to TVD 5.8 so it might be that, out-of-the box, some (or all) of the PSTools suite are blocked as PUPs by the new version of ePolicy Orchestrator. As a result, in your own environment you may need to set exceptions or amend pre-sets.

Hope this helps...

 

[kjv1611]

Yeah, I don't see the use of PSKILL being possible here with the many many restrictions we have, some all our own, others client requirements, etc. So I'll have to definitely think more in the lines of having the code built in based on a timer event.

Thanks for the follow-up, though, that's really interesting.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57