Macro for closing and reopening a workbook read-only

[Remster]

I have a pair of macros that together close and reopen a workbook when the user clicks on a button labelled 'Refresh'. The reason for the macros is that any one of multiple users may have the workbook open for editing while the others have it open read-only, and those who have it open read-only will want to refresh it periodically to make sure they're viewing the most up-to-date version.

Now, here's the hitch. If I have the workbook open read-only and refresh it while no-one else has it open for editing, it doesn't reopen read-only (it reopens for editing). But I don't want it to reopen for editing (I want it to reopen read-only). Is there anything I can add to the macros to make the workbook reopen read-only, or is there a different pair of macros I can use? Here's what I have at the moment:

Macro 1
Sub CloseMe()
'
Application.OnTime Now, "OpenMe"
ThisWorkbook.Close SaveChanges:=False
'
End Sub

Macro 2
Sub OpenMe()
'
End Sub

 

[Airfix9]

Hi,

In your code, where you ask for the book (in my case strWB to be opened), use this code:~

Workbooks.Open strWB, , True

Note the two commas. The "True" part asks the book to open read-only.

 

[Remster]

Hi,

In your code, where you ask for the book (in my case strWB to be opened), use this code:~

Workbooks.Open strWB, , True

Note the two commas. The "True" part asks the book to open read-only.

Many thanks for your reply.

Where would I fit that into the code I already have? (It looks like something I could use to open a different workbook, but how do I use it to reopen the workbook I'm already in?)

 

[Airfix9]

What is the code in Macro 2?

 

[Remster]

What is the code in Macro 2?

Exactly what I've given above (i.e. there isn't any):

Sub OpenMe()
'
End Sub

I don't know how it works, but that combined with Macro 1 above successfully closes and reopens the workbook.

 

[Airfix9]

Cracked it!

Here is the code I placed in the module for Sheet1, edit as required. It uses a command button, also on Sheet1:~

Option Explicit

Public Sub CommandButton1_Click()
    Call CloseIt
End Sub

Public Sub CloseIt()
'
    Application.OnTime Now , "Sheet1.OpenAgain"
    
    With ThisWorkbook
        .Saved = True
        .Close
    End With
'
End Sub
Public Sub OpenAgain()

    Workbooks.Open ThisWorkbook.Path & "\" & ThisWorkbook.Name, , True

End Sub

 

[Remster]

Airfix9,

Many thanks for that, which seems to do the trick!

However, I've discovered a much simpler way of achieving my aim, namely by using a single line from your coding above in new macro (I've deleted my original Macros 1 and 2, and linked my button directly to this new macro):

Sub Refresh()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
'<o></o>
'<o></o>
Workbooks.Open ThisWorkbook.Path & "\" & ThisWorkbook.Name, , True<o></o>
'<o></o>
End Sub
​

I've tested this and it picks up changes from the open-for-editing version as required. The only drawback is that the following message now pops up:

YourWorkbook.xls is already open. Reopening will cause any changes you made to be discarded. Do you want to reopen YourWorkbook.xls? Yes/No
​

This is actually quite a useful message, and it works fine if you click on 'Yes'. But if you click on 'No', you get this error message:

Run-time error '1004': Method 'Open' of object 'Workbooks' failed
​

Have you any idea if there's something I can add to my macro to stop this happening (perhaps a condition that cancels the macro if you click on 'No').

 

[Richard Schollar]

Just add On Error Resume Next before that particular line:

Sub Refresh()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
'<o:p></o:p>
'<o:p></o:p>
On Error Resume Next
Workbooks.Open ThisWorkbook.Path & "\" & ThisWorkbook.Name, , True<o:p></o:p>
'<o:p></o:p>
End Sub

 

[Airfix9]

Got around that by assuming that no saving would be required (is it not a read-only book for the users anyway?)

Sub Refresh()
'
'
    Application.DisplayAlerts = False
    
    Workbooks.Open ThisWorkbook.Path & "\" & ThisWorkbook.Name, , True
    
    Application.DisplayAlerts = True

'
End Sub

 

[Remster]

Thank you! Both methods work perfectly, though I've used Airfix9's (it occurred to me that the user doesn't need to know that the workbook is being reopened, so the initial message is redundant).

Airfix9, for info, the workbook might be open for editing by any one member of a group of users at any given time and read-only for the rest of the group at that time, but open for editing by another member of the group at a different time, etc. So what I have now seems right.