Save over open files

Sjodom

New Member
Joined
Jun 19, 2010
Messages
17
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!
 

Sjodom

New Member
Joined
Jun 19, 2010
Messages
17
Thank you!

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

Sjodom

New Member
Joined
Jun 19, 2010
Messages
17
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

Well-known Member
Joined
Jul 9, 2009
Messages
1,731
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

New Member
Joined
Jun 19, 2010
Messages
17
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!
 

Forum statistics

Threads
1,081,665
Messages
5,360,369
Members
400,583
Latest member
gabivleon

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top