Search code examples
excelvba

Update open read-only version of shared workbook to show saved changes made on editable version of workbook

There is a workbook in a shared drive.

In computer A, I open the workbook, edit, and save it from time to time.

In computer B, the workbook is open in read-only mode.

I set a macro that runs when the workbook is opened in read-only mode, to reopen the workbook with Workbook.Open every 10 seconds with the OnTime method.

I expect the read-only workbook in computer B to show the changes made in computer A after the editable workbook was saved.

The content of the workbook in computer B didn't change.

Solution

  • The Workbook.Open method will open a workbook that is not already open. Excel will not permit you to open two workbooks with the same name.

    What I believe that you want is the Workbook.UpdateFromFile method, which updates a Read-Only file from its source.

    So, code to refresh the the workbook if it is Read-Only would look like this:

    If ThisWorkbook.ReadOnly Then ThisWorkbook.UpdateFromFile