Can Excel VBA auto-update a co-shared workbook every ten seconds?

syl3786 · #1 10-03-2024, 01:20 AM

Hi everyone,

I’m working on a co-shared Excel workbook and exploring how to use VBA for an automatic refresh every five seconds. The goal is to keep the data up-to-date for all users in real-time.

Has anyone implemented something similar? I’d appreciate any advice, especially on handling potential conflicts from multiple users editing the workbook at the same time.

Thanks in advance for your help!

Here’s the code I’ve tried, but it’s not working as expected.

Code:

Private Sub Workbook_Open()
    If Not ActiveWorkbook.MultiUserEditing Then
        ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.FullName, AccessMode:=xlShared
    End If
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    On Error Resume Next
    ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.FullName, AccessMode:=xlShared
End Sub
Public Sub AutoRefresh()
    Application.EnableEvents = False
    On Error GoTo ErrorHandler
    
    ' Refresh all data connections
    ThisWorkbook.RefreshAll
    
    ' Save the workbook
    ThisWorkbook.Save
    
    ' Schedule the next refresh
    Application.OnTime Now + TimeValue("00:00:10"), "AutoRefresh"
    
    Application.EnableEvents = True
    Exit Sub
    
ErrorHandler:
    MsgBox "An error occurred: " & Err.Description
    Application.EnableEvents = True
End Sub

Public Sub StopAutoRefresh()
    On Error Resume Next
    Application.OnTime Now + TimeValue("00:00:10"), "AutoRefresh", , False
End Sub

ArviLaanemets · #2 10-03-2024, 07:01 AM

I'm afraid your problem will be, that when user opens Excel workbook, and makes any changes in it, the Excel this user is working with is a copy of workbook in memory of user's computer (or e.g. in memory of some Terminal Server). The changes are saved to real workbook only when the workbook is saved. And any VBA code is running in memory in system and instance, the this particular user is working from.

And when several users are trying to force the save their own instances of same workbook practically at same time ...

syl3786 · #3 10-03-2024, 07:52 AM

Quote:

Originally Posted by ArviLaanemets

I'm afraid your problem will be, that when user opens Excel workbook, and makes any changes in it, the Excel this user is working with is a copy of workbook in memory of user's computer (or e.g. in memory of some Terminal Server). The changes are saved to real workbook only when the workbook is saved. And any VBA code is running in memory in system and instance, the this particular user is working from.

And when several users are trying to force the save their own instances of same workbook practically at same time ...

I understand your concern regarding simultaneous edits in Excel workbooks. You're right that each user works on a local copy in memory, and changes are only saved to the original file when the user explicitly saves it. This can lead to conflicts when multiple users are trying to save their changes at the same time.

Since only a few members will be actively editing the Excel workbook while the majority will just be reading it, the situation should be more manageable.

Do you have any samples or ideas on how to achieve that?

Logit · #4 10-03-2024, 12:00 PM

Wouldnt simply saving the workbook every 10 seconds suffice ? Placing the vba macro into the original workbbok's "ThisWorkbook" module.

syl3786 · #5 10-03-2024, 05:05 PM

Quote:

Originally Posted by Logit

Wouldnt simply saving the workbook every 10 seconds suffice ? Placing the vba macro into the original workbbok's "ThisWorkbook" module.

The issue is that the current code does not work in a co-shared workbook. Do you have any code that can achieve the following: when a user opens the co-shared Excel workbook, it automatically saves every 10 seconds?

Logit · #6 10-03-2024, 06:45 PM

The following works here. Paste into a Regular Module. When the workbook opens, the saving macro initiates. When the workbook closes, the macro stops.

Code:

Dim TimeToRun

Sub auto_open()
    Call ScheduleCopyPriceOver
End Sub

Sub ScheduleCopyPriceOver()
On Error Resume Next
    TimeToRun = Now + TimeValue("00:00:05")
    DoEvents
    Application.OnTime TimeToRun, "CopyPriceOver"
End Sub

Sub CopyPriceOver()
On Error Resume Next
    ThisWorkbook.Save
    Call ScheduleCopyPriceOver
End Sub

Sub auto_close()
    On Error Resume Next
    Application.DisplayAlerts = True
    Application.OnTime TimeToRun, "CopyPriceOver", , False
End Sub

syl3786 · #7 10-05-2024, 04:24 AM

Quote:

Originally Posted by Logit

The following works here. Paste into a Regular Module. When the workbook opens, the saving macro initiates. When the workbook closes, the macro stops.

Code:

Dim TimeToRun

Sub auto_open()
    Call ScheduleCopyPriceOver
End Sub

Sub ScheduleCopyPriceOver()
On Error Resume Next
    TimeToRun = Now + TimeValue("00:00:05")
    DoEvents
    Application.OnTime TimeToRun, "CopyPriceOver"
End Sub

Sub CopyPriceOver()
On Error Resume Next
    ThisWorkbook.Save
    Call ScheduleCopyPriceOver
End Sub

Sub auto_close()
    On Error Resume Next
    Application.DisplayAlerts = True
    Application.OnTime TimeToRun, "CopyPriceOver", , False
End Sub

Thank you so much for your help!

Logit · #8 10-05-2024, 08:05 AM

You are welcome. Best wishes.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Excel VBA Loop to update formula in certain cells and create copy in new workbook off dynamic range	bwosk	Excel Programming	1	10-07-2021 10:36 AM
Excel 2010 - Shared Workbook greyed out	osheac3*40	Excel	0	03-08-2016 11:40 PM
OneDrive + iPhone Excel: Cannot Update Workbook?	markg2	Excel	0	12-07-2014 09:02 AM
Shared Excel workbook	eguru2	Excel	0	07-12-2011 12:02 AM