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

Question

I have an Excel document that is stored on a SharePoint Server, which I always have open on my computer in read-only mode because I need to refer to it.

Every so often, in order to get the latest changes, I have to close down the file and reload it again. Are there any options within Excel 2007 which allow me to simply refresh a document I have open in read-only mode to the latest version on the server?

Better still, is there a way where this could be done dynamically, without me having to hit refresh?

Answer 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

Answer 2

This might help:

a workbook author can create a workbook that automatically refreshes external data when the workbook is opened by selecting the Refresh data when opening the file check box in the Connection Properties dialog box.

This is where I get it. It's an interesting article. Does this help?