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

 

[SkipVought]

Hi,

In the Workbook_Open event, set a procedure to run OnTime.

For the Sheet that everyone updates, use the Worksheet_Change event to reset the OnTime time to run the procedure, so that after either OPEN or CHANGE the user has x minutes before the workbook Saves and Closes.

Said procedure should then Save and Close the workbook when it runs.

Skip,

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

 

[keepingbusy]

Skip

This is new to me....

I have a spreadsheet open
I have ALT+F11 to show the VBproject page
There are Sheet1, 2, 3 etc then ThisWorkbook
If I right click on the ThisWorkbook > Open code, I have another window

Where do I go from there?

Regards

 

[SkipVought]

The VB Project displays the workbook project objects.

Doulbe clicking any of these objects will open the code sheet for that object.

In the upper LH of the Code Sheet is a DropDown, when you can select other objects within that object. If it is the ThisWorkbook object, then the Workbook object can be selected. With the Workbook object selected, look at the DropDown to the right. You will see listed the Workbook Events. Likewise with Worksheet objects.

If you select the Open event, the code stub for Workbook_Open will be displayed. It is here that you will start the OnTime for your Save/Close procedure.

Code the Save/Close procedure in a Module. You can Insert a module into your VB Project and code it something like this...

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

Skip,

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

 

[keepingbusy]

Ok Skip

I have found that as you described it. I suppose my next question has to be, is the code you have supplied do the job of auto closing excel after a specified time of inactivity and if so, what do I need to change to force excel to close after say five minutes?

Thank you

 

[SkipVought]

Have you looked at OnTime?

Skip,

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

 

[keepingbusy]

In the drop down box, there is no OnTime

KB

 

[SkipVought]

VBA Help

Skip,

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

 

[keepingbusy]

Hi Skip / All,

Apologies that this is an old thread but I am just missing one small part (I think) to resolve my issue.

Here is what I have done so far:

Created a new Excel File
ALT+F11 to show the VBproject page
Sheet1, 2, 3 etc then ThisWorkbook
Right click on the ThisWorkbook > Open code
Added the following:

Sub SaveCloseWorkbook()
  Application.DisplayAlerts = False
  [b]Application.OnTime Now + TimeValue("00:00:15"), "my_procedure)[/b]
  ThisWorkbook.Save
  ThisWorkbook.Close
End Sub

Saved the file

When you run the excel file I get an error message:

Compile error: Expected End Sub

Could someone please explain what is missing here and what is the alternative for "my_procedure"

The VBA help only gives the example I have shown.

Many thanks

Lee

Visual FoxPro Version 9

http://www.customsoftware.co.uk

 

[PHV]

Application.OnTime Now + TimeValue("00:00:15"), "my_procedure[!]"[/!]

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

 

[keepingbusy]

The "my_procedure" was a typo on my part. I missed the "

It should read:

Application.OnTime Now + TimeValue("00:00:15"), "my_procedure)"

Visual FoxPro Version 9

http://www.customsoftware.co.uk

 

[PHV]

So, get rid of the closing parenthesis.

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

 

[keepingbusy]

Sorry I am not having a good day with my typing. It should read:

Application.OnTime Now + TimeValue("00:00:15"), "my_procedure"

Visual FoxPro Version 9

http://www.customsoftware.co.uk

 

[PHV]

Compile error: Expected End Sub
Which line of code is highlighted when in Debug mode ?

What is the code of your procedure named my_procedure ?

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

 

[SkipVought]

If you CLOSE the workbook, there is NOTHING RUNNING!!!

NADA!!!

Your workbook must remain in memory -- OPEN!!!

Skip,

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

 

[keepingbusy]

Sorry guys, now I'm lost. In answer to PHV, the offedning line is:

Sub SaveCloseWorkbook()

I do not have a procedure named my_procedure as this example came from the VBA help file.

Skip, I'm guessing here that one of the code lines is incorrect?

Just to reiterate, all I want to do is open an excel file and get it to save and close automatically after a set period of time.

Many thanks



Visual FoxPro Version 9

http://www.customsoftware.co.uk

 

[SkipVought]

Just to reiterate, all I want to do is open an excel file and get it to save and close automatically after a set period of time.

Private Sub Workbook_[b]Open[/b]()
  application.ontime Now + TimeValue("00:00:15"), "my_procedure"
End Sub

where procedure my_procedure, SAVES & CLOSES the workbook

Skip,

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

 

[keepingbusy]

Skip

where procedure my_procedure, SAVES & CLOSES the workbook

Sorry if this sounds thick, but now I need to know how do you close the workbook automatically in a procedure?

I am not at all used to VBA so any guidance would be appreciated.


Visual FoxPro Version 9

http://www.customsoftware.co.uk

 

[SkipVought]

Thisworkbook.close

Skip,

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

 

[SkipVought]

Previously I posted a procedure SaveCloseWorkbook

THAT is the "my_procedure." Use SaveCloseWorkbook instead.

Skip,

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