Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 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?

share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.