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