Microsoft Office Forums - View Single Post - Can Excel VBA auto-update a co-shared workbook every ten seconds?

syl3786 · 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