Refresh a Currently open Excel Sheet?

[Bundy]

Hi Guys,

Here's scenario.

Person A Opens a document & updates/Saves it all day.

Person B,C,D Open the same document as Read Only.

Is there any VBA Code that can be used to refresh the read only version of B,C,D as maybe a button added to the sheet? (or any other options to use?)

 

[Smitty]

Nope.

Even with a shared workbook, the other users need to close and reopen the wb in order to see saved changes.

It is possible to have a real-time wb using Groove, but I haven't tried it.

HTH,

 

[Bundy]

Currently they:

Main Workbook--->Copy saved by the main workbook<--------Linked to copy Workbook

Sort of Dangerous, so I was looking for a better solution.

 

[erik.van.geit]

Hello,

Would this help?
Run GoodbyeAndHello

Sub GoodbyeAndHello()
    Application.OnTime earliesttime:=Now(), procedure:="OpenMe"
    ThisWorkbook.Close savechanges:=False
End Sub
 
Private Sub OpenMe()
'you do not need next line
    MsgBox "Hello again"
End Sub

kind regards,
Erik

 

[Bundy]

Here's the Code we used, with a few small changes.
A1 now show's when it was updated or saved, depending on if it is read only or full access.
If the person has full access, it will only save it and not lose changes.

Thanks All!

Sub TimerRoutine()
    If ActiveWorkbook.ReadOnly Then
        Application.OnTime earliesttime:=Now(), procedure:="OpenMe"
        
        Application.DisplayAlerts = False
        
        ThisWorkbook.Close savechanges:=False
    Else
        ActiveWorkbook.Save
        ActiveSheet.Range("A1").Value = "Last Saved " & Now()
    End If
End Sub
 
Private Sub OpenMe()
    ActiveSheet.Range("A1").Value = "Last updated " & Now()
    Application.DisplayAlerts = True
End Sub

 

[erik.van.geit]

I had another idea and this might be overkill, but anyway here it is.
To avoid the screen to get empty or changing to other window, you can do this.

Option Explicit
 
Const TmpName As String = "test.xls"
Const TmpPath As String = "C:\Windows\Temp\" & TmpName
 
Sub GoodbyeAndHello()
ActiveSheet.Copy
ActiveWorkbook.SaveAs TmpPath
Application.OnTime earliesttime:=Now(), procedure:="OpenMe"
ThisWorkbook.Close savechanges:=False
End Sub
 
Private Sub OpenMe()
Workbooks(TmpName).Close
Kill TmpPath
End Sub

 

[mathewktg]

I had another idea and this might be overkill, but anyway here it is.
To avoid the screen to get empty or changing to other window, you can do this.

Option Explicit
 
Const TmpName As String = "test.xls"
Const TmpPath As String = "C:\Windows\Temp\" & TmpName
 
Sub GoodbyeAndHello()
ActiveSheet.Copy
ActiveWorkbook.SaveAs TmpPath
Application.OnTime earliesttime:=Now(), procedure:="OpenMe"
ThisWorkbook.Close savechanges:=False
End Sub
 
Private Sub OpenMe()
Workbooks(TmpName).Close
Kill TmpPath
End Sub

Can't we all use the update button the reviewing toolbar (Excel 2003) and refresh the shared workbook? Ofcourse the master workbook need to be saved.

 

[erik.van.geit]

Hi,
WELCOME to MrExcel!
Your question is not clear to me.
You replied to an old thread. You would better start one of your own if you do not find an answer using the search function of this forum.
You can always add a reference to this thread if you want.
kind regards,
Erik

 

[Sahak]

Hi Erik,

I have almost the same issue with reopening shared file (to update). I would like to know what is the difference between your code

Sub GoodbyeAndHello()
    Application.OnTime earliesttime:=Now(), procedure:="OpenMe"
    ThisWorkbook.Close savechanges:=False
End Sub
 
Private Sub OpenMe()
'you do not need next line
    MsgBox "Hello again"
End Sub

And

Sub CloseAndOpen()
    Workbooks("Db.xlsb").Close savechanges:=False
    Workbooks.Open "C:\Users\Administrator\Desktop\Db.xlsb"
End Sub

Will your code close & reopen faster?

Thank you in advance & god bless you.

 

[erik.van.geit]

Hi

Both codes have not the same purpose.

Your code cannot be run from the workbook itself. You code, if place in the workbook "Db.xlsb" would stop running with the "close" line.
My code is to close and reopen "ThisWorkbook".

kind regards,
Erik