Grubeaters
asked on
Shared worksheet auto refresh
I have an Excel worksheet that is updated by one or two people on a network but is viewed by several people in read only mode. My question is how can the people that have the worksheet open in read only mode have their worksheet auto refresh so they can view the changes done by the people with full access. Right now they have to close the worksheet and reopen to view the changes.
Is this a shared workbook?
Hello Grubeaters
not sure if a read-only workbook/sheet can refresh it has to write values let alone the question of refreshing an open workbook
that would mean that the *copy* in memory of the client has to be thrown awaya and recreated from the current hard copy on disk/server
not hte answer you would like but i don't think this is going to work
HAGD:O)Bruintje
not sure if a read-only workbook/sheet can refresh it has to write values let alone the question of refreshing an open workbook
that would mean that the *copy* in memory of the client has to be thrown awaya and recreated from the current hard copy on disk/server
not hte answer you would like but i don't think this is going to work
HAGD:O)Bruintje
Only 2 possibilities I can see. (1) Make the workbook shared and you have limited ability to show changes. But it appears the shortest refresh interval (in XL2000 at least) is 5 min. Also, I don't see any provision for sharing as read-only. (2) You might be able to create a linked object in another workbook. However, I can't seem to get the picture to refresh properly from another login.
Jeff, this is working? > But it appears the shortest refresh interval (in XL2000 at least) is 5 min
is the sheet refreshing it self on a autosave or something?
Brian
is the sheet refreshing it self on a autosave or something?
Brian
Brian, to be honest, I had to leave the office before the 5 min. were up, so I haven't seen with my own eyes. The dialog tab says it can update changes when file is saved or automatically every xx minutes. You mean this feature doesn't do what it says?! Microsoft wouldn't do that - would they???!
Jeff
Jeff
no no i didn't knew about it i'm always learning new things here, but now i've to test it myself to see it
Well, I just tried it again using "update on save," and either it just doesn't work or the lag time (I'm going over a VPN today) is significant. Back to the drawing board.
Grubeaters -
On closer inspection, I see "update on save" means I will see other users' changes when I save the shared workbook. Since you probably don't want everybody saving the workbook, this approach is probably out.
Another possibility is to create a linked object (Insert > Object > Create From File tab > Browse to file and check "Link to file") to the source workbook. However, using this approach, I've had only occasional success getting OLE to refresh properly (ActiveSheet.OLEObjects(1)
A 3rd approach sounds crude but works. Save the master workbook as read-only recommended. Include the following code, which will check at regular intervals (determined by value of CN_Freq) to see if file has been saved. If it has, the file is re-opened. If a user has the file open read-write, the code isn't executed.
Const CN_1Sec = 1 / 24 / 3600
Const CN_Freq = 10
Dim wbPath As String, lastMod As Date
Sub auto_Open()
With ThisWorkbook
If .ReadOnly Then
wbPath = .Path & "\" & .Name
lastMod = FileDateTime(wbPath)
Application.OnTime earliesttime:=Now() + CN_Freq * CN_1Sec, procedure:="chkUpdate"
End If
End With
End Sub
Private Sub chkUpdate()
modTime = FileDateTime(wbPath)
If modTime > lastMod Then
Application.DisplayAlerts = False
Workbooks.Open wbPath, ReadOnly:=True, updatelinks:=0
Else
Application.OnTime earliesttime:=Now() + CN_Freq * CN_1Sec, procedure:="chkUpdate"
End If
End Sub
On closer inspection, I see "update on save" means I will see other users' changes when I save the shared workbook. Since you probably don't want everybody saving the workbook, this approach is probably out.
Another possibility is to create a linked object (Insert > Object > Create From File tab > Browse to file and check "Link to file") to the source workbook. However, using this approach, I've had only occasional success getting OLE to refresh properly (ActiveSheet.OLEObjects(1)
A 3rd approach sounds crude but works. Save the master workbook as read-only recommended. Include the following code, which will check at regular intervals (determined by value of CN_Freq) to see if file has been saved. If it has, the file is re-opened. If a user has the file open read-write, the code isn't executed.
Const CN_1Sec = 1 / 24 / 3600
Const CN_Freq = 10
Dim wbPath As String, lastMod As Date
Sub auto_Open()
With ThisWorkbook
If .ReadOnly Then
wbPath = .Path & "\" & .Name
lastMod = FileDateTime(wbPath)
Application.OnTime earliesttime:=Now() + CN_Freq * CN_1Sec, procedure:="chkUpdate"
End If
End With
End Sub
Private Sub chkUpdate()
modTime = FileDateTime(wbPath)
If modTime > lastMod Then
Application.DisplayAlerts = False
Workbooks.Open wbPath, ReadOnly:=True, updatelinks:=0
Else
Application.OnTime earliesttime:=Now() + CN_Freq * CN_1Sec, procedure:="chkUpdate"
End If
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hello Jmfairchild, any update on this? :O)Bruintje
ASKER
Thanks, I used alternative #3 for now and it appears to be working fine for us.
Hello Grubeaters you accepted a non-answer from yourself? Jeff did all the work here
:O)Bruintje
:O)Bruintje