Auto Save Shared Workbook and Update Display

We are currently utilizing a shared workbook (yes, I've read the inherent problems with this). This workbook needs to automatically save any changes and refresh itself every minute. We are seeking an answer to this goal, perhaps utilizing VBA code ?

We have researched the forums and attempted numerous snippets of VBA code and different settings already available in Excel (ver 2010) without success. And, we have tried using the Advanced Settings (Shared Workbook / Advanced Settings (5 mins)) .... however the display of newly added data is not updated on all viewing terminals unless we close the shared workbook on those viewing terminals and then re-open.

We are stumped !

Any suggestions ? Thank you so much for your assistance.

You might want to also include this after thisworkbook.save

Please Login or Register  to view this content.

billstpierre79

Thank you for the code snippets. The code functions as designed but only for the first 60 second iteration, after the workbook has been opened. It fails to continue to update at 120 seconds and later unless the viewing party closes the workbook on their terminal then re-open the workbook .. wherein it will update only once after 60 seconds.

I am not certain using a "loop" would be the correct approach to continue the update action. Also, reviewing other posts on the Forum concerning similar attempts of auto-updates, I can only find instances where others experienced the same issue.

Do you have a suggestion how to keep the updates performing after the first 60 second mark ?

Thank you for your assistance !

Try

Please Login or Register  to view this content.

And put this in ThisWorkbook module

Please Login or Register  to view this content.

It worked for me.

The last snippet functions as desired. I deeply appreciate your help.

If, there are other Excel workbooks left open when the above mentioned workbook is closed, the timer continues to function. On my system, after one minute of closing the workbook, it will re-open itself and remain displayed.

My research on the forum indicates a possible use of the following Sub placed in a Module. Do you forsee known problems with utilizing this to force the timer to stop when the indicated workbook is closed ?

Sub ShutDown()
ThisWorkbook.Save
ThisWorkbook.Close
End Sub

(PS: I've done some VB programming .. hobby basis ... and wonder what would be a good resource publication for VBA coding commands.)

I'll answer my question (sorry) ... seems what I am seeking is the ".OnTime" command.

Hmmm..... more searching.

Searching the Forum as well as the internet resources, I have located several discussions relating to the following Sub snippet. I've attempted to utilize this code in a Module as well as ThisWorkBook without success. The stopping of the Windows timer should occur whenever the user exits the workbook.

Help me understand my error please. Thank you !

Sub StopTimer()
On Error Resume Next
Application.OnTime Schedule:=False
End Sub

My research last night resulted in the the following from Chip Pearson - alot of examples from others have referred to this as well:

You need to pass the EXACT time of the timer back to OnTime in order to
cancel the timer. Therefore, you should create a public variable outside of
any procedure and store the time in that variable.

Public RunWhen As Double

Sub RunMacro()
RunWhen = Now + TimeValue("00:00:05")
Application.RunWhen, "OnTimeMacro"
End Sub

Sub byebye()
Application.OnTime EarliestTime:=RunWhen, _
Procedure:="my_Procedure", Schedule:=False
MsgBox "Bye Bye"
End Sub

See http://www.cpearson.com/excel/ontime.aspx for full details.


Therefore, I've modified the code (for my project) to read:

Public RunWhen As Double

Sub RunMacro()
RunWhen = Now + TimeValue("00:01:00")
Application.RunWhen, "WorkBook_Open"
End Sub

Sub byebye()
Application.OnTime EarliestTime:=RunWhen, _
Procedure:="AutoSave", Schedule:=False
End Sub

My problem ... I'm not certain where to place this code, nor am I certain the syntax is correct for my project. The users & viewers of the workbook open a sheet named "Dashboard" ... in case that has implication for the above snippet.

Looking forward to your assistance. Thank you !

Ok, here is what I am attempting to use but the Application.OnTime is not stopping / killing the timer. Please advise what is wrong with this code:

In ThisWorkBook

Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:01:00"), "AutoSave"
End Sub

Private Sub Workbook_Close()
Application.OnTime EarliestTime:=Now + TimeValue("00:01:00"), Procedure:=AutoSave, Schedule:=False
End Sub

In Module1

Sub AutoSave()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
ThisWorkbook.Save
Application.OnTime Now + TimeValue("00:01:00"), "AutoSave"
Workbooks.Open ActiveWorkbook.FullName '<=====this will reopen the workbook to show 'any changes that have been done by other users
Application.DisplayAlerts = True

End Sub

The hardest part is determining the run time. One option would be to use an if statement at the begining of your code.

Please Login or Register  to view this content.

I am having a difficult time understanding the logic.

"If it is not my opened workbook, then exit (the sub)" That seems to imply another way of setting a Loop.

Another way of stating the above would be: "If this is my opened workbook, stay in the sub (keep the timer running)"

Your thoughts ?

You need to change "yourworkbookname.xlsm" with the name of your workbook. This will check the active workbook name. If it is not the one that the code is intended for it will not fire off. And it won't try to open the workbook again.

Try this.

ThisWorkbook module

Please Login or Register  to view this content.

regular module

Please Login or Register  to view this content.

If you close the workbook it should stop running because the active workbook is not the preselected workbook name. It will try to run but it will stop looping after the end sub. Let me know if you have any questions.

Thanks for the solution!