How can I refresh a document I have open in Excel in read-only mode?

Solution 1:

Yuval's solution might suffice, but only if changes are limited to cell contents. Inquirer did not indicate if this is the case. Nonetheless: what if the change you want to pick up is the addition of (maybe even removal of) worksheets in the workbook?

Sort of brittle and yucky solution: store a macro in your hidden PERSONAL.XLS(B) to perform a periodic (by rescheduling itself) workbook close and reopen. PERSONAL.XLS(B) should be found at %USERPROFILE%\AppData\Roaming\Microsoft\Excel\XLSTART\ )

Sub wkbRefresher()
    Dim refreshedWorkbook As Workbook
    Dim WkBks As Workbooks

    'full filepath
    fPath = "c:\tmp\mutatingWorkbook.xls"
    'in HH:MM:SS format:
    refreshInterval = "00:05:00"

    For i = 1 To Application.Workbooks.Count
        Debug.Print (Application.Workbooks.Item(i).FullName)
        If LCase(Application.Workbooks.Item(i).FullName) = LCase(fPath) Then
            Debug.Print ("  Yep thats the one! Lets refresh it.")
            Application.Workbooks.Item(i).Close
            'refreshedWorkbook = WkBks.Open(fPath, True, True)
            Set refreshedWorkbook = Excel.Application.Workbooks.Open(fPath, True, True)
        End If
    Next i

    ' Use at your own risk: this is an "asynchronous", the execution cannot be stopped by merely pressing the stop button in the VBA interface.
    ' You might have to do something like put a break marker on the line OnTime line so that next time around, it doesn't respawn itself.
    Application.OnTime Now + TimeValue(refreshInterval), "wkbRefresher"
End Sub

Of course the above sub could be parameterized and/or you could attach it to a custom toolbar button or something. Since workbook saving saves the active sheet, active cell, etc. state information, you might also want to include a few lines to save your preferred active sheet name and reactivate it each time after a re-open.

References:

http://office.microsoft.com/en-us/excel-help/run-a-macro-HP010342865.aspx http://msdn.microsoft.com/en-us/library/office/ff196165(v=office.14).aspx

Though I didn't vet it all out, this seems to be a very helpful introduction if you've not heard of PERSONAL.XLS(B): http://www.rondebruin.nl/win/personal.htm