excelvba

Workbook.UpdateFromFile stops runtime

If ThisWorkbook.ReadOnly = True Then
    ThisWorkbook.UpdateFromFile
    Stop
End If

When nothing's changed since the workbook was opened, the Stop statement will be executed as the in-memory copy of the workbook wasn't reloaded.

But if the in-memory copy of the workbook does get updated, the runtime will just stop and code after .UpdateFromFile won't be executed.

Is there any fix of this? I've tried workbook events like Workbook.Open or Workbook.Activate though none of them get triggered after .UpdateFromFile.

Solution

  • Workbook.UpdateFromFile is a full reload: if the copy on disk is newer, Excel discards the current in‑memory workbook, loads the file again and re‑creates its VBA project.
    Because the project that was running no longer exists, the current call stack is abandoned and execution simply stops – exactly what you are seeing. No workbook‑level events (Open, Activate, SheetChange, …) are raised during the reload, so you can’t trap it that way. (https://learn.microsoft.com/en-us/office/vba/api/excel.workbook.updatefromfile)

    Try to use Application.OnTime

    
Sub SyncAndContinue()

    If ThisWorkbook.ReadOnly Then                     
        '1. hand off the real work to be run later …
        Application.OnTime Now, _
                         "'" & ThisWorkbook.Name & "'!ContinueWork"

        '2. refresh the workbook (this kills the current code)
        ThisWorkbook.UpdateFromFile
        Exit Sub                                      
    End If

    'If the workbook wasn’t reloaded (no newer file on disk),
    
    ContinueWork
End Sub


Private Sub ContinueWork()
    '<<< put everything that should happen AFTER the file is up to date here >>>
    MsgBox "Workbook is current – continuing processing …"
End Sub