Save over open files

[Sjodom]

Hello,

I am having difficulty with clearing out data from a third party. Here is what I have and what I want:

I have users that save data in workbook B throughout the day. At the end of their shift, they are supposed to close down the workbook so that workbook A can collect the data remotely and then clear it for the following morning. However, there are sometimes this isn't the case, and accessing the file by its physical location to close it down is not an option.

I have tried to schedule the Task Manager to do this, but it has some user errors that we would like to avoid. It works, but there are some days different people collect the data in the morning. Consistency isn't required.

I would LOVE to be able to remotely close down that sheet with it still open, save over the file with the cleared out template, or even just display what files are still open so that we can clear them out the following day.

Any ideas? I have searched and come up empty so far.

Thank you!

 

[daverunt]

Have a look at this.
http://www.teachexcel.com/free-excel-macros/m-13,close-workbook-time-limit.html

The workbook is closed after a period of inactivity.

 

[Sjodom]

Thank you!

I am placing it into effect now and will see the results.

 

[Sjodom]

Hello,

This worked almost to perfection! It closed the files down when there was no activity past a certain time; however, when workbook A goes to open the file (workbook B), workbook B's ThisWorkbook macro takes over and waits for the specified time before shutting down.

Do you know any way to bypass this?

 

[daverunt]

I don't know as I am no expert. You did just ask for ideas?

Another idea is to look into is running a separate macro file at a specified time in the same location as workbook B to close the file if the macro finds it open?

Have a look at Excel OnTime events


And this code can be adapted to check the file is Open rather than closed.

http://www.vbaexpress.com/kb/getarticle.php?kb_id=468

 

[Sjodom]

I still am having difficulty with a remote close/disconnecting. But I was able to create a list of the files still open to notify the users before continuing. This way they can try to manually close them down before performing the function.

Thank you for your help!