Force excel to close 3

[keepingbusy]

Previously posted in Microsoft Office forum:


From time to time, users of a certain Excel document on our server, leave it open which blocks others from making any changes to it (Read only).

Is there a way to close the open document, even without saving, if there is no activity from the person who opened it, say after 10 minutes or whatever time is appropriate?

Many thanks

KB

 

[keepingbusy]

Ok, it looks as though I have got beyond the error message but still not sure about how to close the xls file.

I have tried a few combinations such as:

Private Sub Workbook_Open()
  application.ontime Now + TimeValue("00:00:15"), "Thisworkbook.close"
End Sub

and

Private Sub Workbook_Open()
  application.ontime Now + TimeValue("00:00:15"), "SaveCloseWorkbook"
End Sub

Both these go the length of time (15 seconds for this exampe) and then a messagebox appears saying:

The macro "c:\myfiles\testdocument.xls'Thisworkbook.close' cannot be found

or

The macro "c:\myfiles\testdocument.xls'SaveCloeWorkbook' cannot be found

I would be grateful if someone could please explain what I am missing.

Thank you

Lee

Visual FoxPro Version 9

http://www.customsoftware.co.uk

 

[SkipVought]

Exactly where do you have each of your procedures?

Please be specific.

Name the procedure, along with the module, workbook object or worksheet object.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[keepingbusy]

Skip

The above code is stored in the VBA window having right clicked on Thisworkbook after pressing ALT+F11

The two drop down fields in the code window are (left) Workbook (Right) Open

As I have mentioned I am completely new to this and don't have much knowledge of VBA.

I appreciate your time.

Lee

Visual FoxPro Version 9

http://www.customsoftware.co.uk

 

[SkipVought]

In the ThisWorkbook Object Code Window, you should only have the call in the Workbook_Open Event.

CUT the other procedures and PASTE into a MODULE.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[keepingbusy]

Sorry Skip

I am going to forget this project I am finding it confusing and not sure now what is going where.

I will look at this sometime again in the future.

As you will appreciate, for someone who does this day in day out it is easy. Ask me about VisFox 9 and perhaps I can answer the question.

Thanks to those who posted on this thread.



Visual FoxPro Version 9

http://www.customsoftware.co.uk

 

[keepingbusy]

After some deep searching I found the following code and managed to get the spreadsheet to close. This is what I did.

Created a spreadsheet
Opened the VBA editor (ALT + F11)
Right click ThisWorkbook
Used the following code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  SetBack
End Sub

Private Sub Workbook_Open()
  StartProc
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
  StartProc
End Sub

Then I right clicked on Modules > Insert > Module

Then I used the following in that window (5 seconds to test):

Dim datEnd As Date
Public Const datIntervall As Date = "00:00:05"

Sub StartProc()
  On Error Resume Next
  Application.OnTime EarliestTime:=datEnd, Procedure:="EndProc", Schedule:=False
  datEnd = Now + datIntervall
  Application.OnTime datEnd, "EndProc"
End Sub

Sub EndProc()
  ThisWorkbook.Close True
End Sub

Sub SetBack()
  Application.OnTime EarliestTime:=datEnd, Procedure:="EndProc", Schedule:=False
End Sub

So this works fine after 5 seconds (I can change the time to suit later).

So now I am at this stage, it would be useful to close Excel automatically, not just the Workbook

Thanks for the posts (and inspiration)

Visual FoxPro Version 9

http://www.customsoftware.co.uk

 

[PHV]

it would be useful to close Excel automatically, not just the Workbook
Sub EndProc()
ThisWorkbook.Close True
[!]Application.Quit[/!]
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kjv1611]

I think the only part you were missing originally was putting Skip's original VBA code in a separate module, like you did with your last post. If I'm reading this thread correctly, you put in the "this sheet" code, but didn't add the module and associated code at the time.

Of course, the new code you have has more to it, and if it works, go with it. PHV's suggestion should have you fixed there. If that doesn't seem to do as you want, go back to square one, read back through the thread, and follow Skip's original advice. Just for clarity of reference, I'll sum that up here (as far as possibly a re-ordering of events might clarify the topic):

1. Create a new module under "this workbook" - rename it if you wish, but Module1 or Module2 or whatever number given should be fine.

2. In THAT new module, put the code:

Sub SaveCloseWorkbook()
  Application.DisplayAlerts = False
  ThisWorkbook.Save
  ThisWorkbook.Close
End Sub

3. THEN in your ThisWorkbook code page, the following will work:

Private Sub Workbook_Open()
  application.ontime Now + TimeValue("00:00:15"), "SaveCloseWorkbook"
End Sub

4. And of course, like PHV gave in his last post, you could also add Application.Quit at the end of the first module...

5. And you need a Application.SetWarnings True as well..

So... the Module code would actually be:

Sub SaveCloseWorkbook()
  Application.DisplayAlerts = False
  ThisWorkbook.Save
  ThisWorkbook.Close
  Application.DisplayAlerts = True
  Application.Quit
End Sub

Let us know what ends up working for you either way. One key point is to not get frustrated. If something doesn't work, it could be b/c of a simple typo or perhaps one tiny thing being in the wrong place. Either way, it is not anything worth panicking over.

 

[keepingbusy]

kjv1611

Thank you for your post.

Having tried your additional suggestion, Excel still does not close down automatically. However, I am more than happy (and less frustrated) with this sollution.

The whole purpose of this was to close spreadsheets automatically that were left open by individuals who either forgot or quitely hogged the file.

Thanks again to all.

Visual FoxPro Version 9

http://www.customsoftware.co.uk

 

[kjv1611]

Well, it may be as I was initially thinking... the .Quit command needs to be in a different location...

Try cutting it out of the current location, and pasting it here:

Private Sub Workbook_Open()
  application.ontime Now + TimeValue("00:00:15"), "SaveCloseWorkbook"
  [BLUE]Application.Quit[/BLUE]
End Sub

And there is one other thing I just though of for this scnerio. I'm not sure you want to just totally close Excel anyway. What if the user has other workbooks open? If you force close the application, they may lose unsaved changes in the other workbooks, or at least get really annoyed... So, for the killing of the application, you may need to include some conditional code to see if any other workbooks are open before quitting... and if anything is open, don't quit the application.

 

[SkipVought]

The quit method might not be appreciated if the user also has other Excel workbooks open in the same instance of Excel.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kjv1611]

And there is one other thing I just though of for this scnerio. I'm not sure you want to just totally close Excel anyway. [highlight]What if the user has other workbooks open? If you force close the application, they may lose unsaved changes in the other workbooks, or at least get really annoyed... So, for the killing of the application, you may need to include some conditional code to see if any other workbooks are open before quitting... and if anything is open, don't quit the application.[/highlight]

But Skip's post is MUCH more succinct..

The quit method might not be appreciated if the user also has other Excel workbooks open in the same instance of Excel.

 

[keepingbusy]

kjv1611 / Skip

I agree. Closing down Excel completely could cause a problem but am I right in saying that the procedure shown on this thread would only apply to a particular spreadsheet that had the coding present?

If users are creating other spreadsheets, then the VBA code wouldn't apply or am I wrong?

Lee

Visual FoxPro Version 9

http://www.customsoftware.co.uk

 

[SkipVought]

ThisWorkbook.Close True

refers only to the workbook in which the code is running.

If the user has other workbooks open, the other workbooks would remain open. If the user has no other workbooks open, only the Excel application remains open.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kjv1611]

Here we go... the conditional code I was talking about...

Change this:

Private Sub Workbook_Open()
  application.ontime Now + TimeValue("00:00:15"), "SaveCloseWorkbook"
  Application.Quit
End Sub

To this:

Private Sub Workbook_Open()
  application.ontime Now + TimeValue("00:00:15"), "SaveCloseWorkbook"
  [blue]If Application.Workbooks.Count = 1 Then[/blue]
    Application.Quit
  [blue]End If[/blue]
End Sub

That way, it'll close Excel, if this instance of Excel only has the one workbook open, but it'll not mess with Excel if there are any other workbooks open...

To see, try it out, and post back... try it while you have just the one workbook open, and then try it out when you have 2 or more workbooks open in the same instance of Excel.

 

[kjv1611]

Actually, you know what, I just got to thinking.... that may not work the way I thought...

By the point that code runs, the workbook is already closed, so in this case, it would close if you have only 1 other workbook open, but this one isn't closed... I guess....

BUT you may can use it in the BeforeClose event of said workbook... I tried it here, but couldn't get anything to work... yet..

 

[kjv1611]

Fooey! Well, best I can find so far, I cannot seem to get the thing to close... but wait.... maybe I can... I think in Excel 2007 it has to do with the PERSONAL.XLS file still being open... hmm... but if I close that file... well, I'll tinker, but you may be better off just leaving Excel's Application alone.

 

[kjv1611]

Well, more tinkering, no fruits....

I mean, at first I figure there has got to be a way, but no matter what I do so far, I can't get Excel to say bu-bye..

Hmm... I'll try just a strait macro and see...

 

[kjv1611]

Okay, hmm, strange, I can run a strait ...Quit from a PERSONAL.XLS Module... I wonder why it doesn't work the same otherwise..... and I wonder if I put THAT in a module, and just call it from the conditional one..... or perhaps it's b/c I set it to Private Sub intead of just "sub"...

we shall see...

I just had to go and get interested in this one...

 

[kjv1611]

Well, NOW I think it's a SharePoint 2007 Add-In that's installed with Excel 2007 here at work that keeps me from effectively closing Excel. I could be wrong, but regardless, can't get it to close unless I ONLY run code from a PERSONAL.XLS module macro with one line - Application.quit... if any other code runs with it, it just sits there and looks at me with its blank gray face.

Oh well... maybe your's will work if you don't have the SharePoint Piece.