Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Macro for closing and reopening a workbook read-only

This is a discussion on Macro for closing and reopening a workbook read-only within the Excel Questions forums, part of the Question Forums category; I have a pair of macros that together close and reopen a workbook when the user clicks on a button ...

  1. #1
    New Member
    Join Date
    Apr 2009
    Posts
    22

    Default Macro for closing and reopening a workbook read-only

    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

  2. #2
    Board Regular Airfix9's Avatar
    Join Date
    Sep 2005
    Location
    I was born under a wandrin' star
    Posts
    790

    Default Re: Macro for closing and reopening a workbook read-only

    Hi,

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

    Code:
    Workbooks.Open strWB, , True

    Note the two commas. The "True" part asks the book to open read-only.
    The more you learn, the more you learn that there's more to learn.

    Every time I make it more idiot-proof, they build a better idiot!

  3. #3
    New Member
    Join Date
    Apr 2009
    Posts
    22

    Default Re: Macro for closing and reopening a workbook read-only

    Quote Originally Posted by Airfix9 View Post
    Hi,

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

    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?)

  4. #4
    Board Regular Airfix9's Avatar
    Join Date
    Sep 2005
    Location
    I was born under a wandrin' star
    Posts
    790

    Default Re: Macro for closing and reopening a workbook read-only

    What is the code in Macro 2?
    The more you learn, the more you learn that there's more to learn.

    Every time I make it more idiot-proof, they build a better idiot!

  5. #5
    New Member
    Join Date
    Apr 2009
    Posts
    22

    Default Re: Macro for closing and reopening a workbook read-only

    Quote Originally Posted by Airfix9 View Post
    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.

  6. #6
    Board Regular Airfix9's Avatar
    Join Date
    Sep 2005
    Location
    I was born under a wandrin' star
    Posts
    790

    Default Re: Macro for closing and reopening a workbook read-only

    Cracked it!

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

    Code:
    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
    The more you learn, the more you learn that there's more to learn.

    Every time I make it more idiot-proof, they build a better idiot!

  7. #7
    New Member
    Join Date
    Apr 2009
    Posts
    22

    Default Re: Macro for closing and reopening a workbook read-only

    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()
    '
    '
    Workbooks.Open ThisWorkbook.Path & "\" & ThisWorkbook.Name, , True
    '
    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').

  8. #8
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,678

    Default Re: Macro for closing and reopening a workbook read-only

    Just add On Error Resume Next before that particular line:

    Code:
    Sub Refresh()
    '
    '
    On Error Resume Next
    Workbooks.Open ThisWorkbook.Path & "\" & ThisWorkbook.Name, , True
    '
    End Sub
    Richard Schollar

    Using xl2013

  9. #9
    Board Regular Airfix9's Avatar
    Join Date
    Sep 2005
    Location
    I was born under a wandrin' star
    Posts
    790

    Default Re: Macro for closing and reopening a workbook read-only

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

    Code:
    Sub Refresh()
    '
    '
        Application.DisplayAlerts = False
        
        Workbooks.Open ThisWorkbook.Path & "\" & ThisWorkbook.Name, , True
        
        Application.DisplayAlerts = True
    
    '
    End Sub
    The more you learn, the more you learn that there's more to learn.

    Every time I make it more idiot-proof, they build a better idiot!

  10. #10
    New Member
    Join Date
    Apr 2009
    Posts
    22

    Default Re: Macro for closing and reopening a workbook read-only

    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.

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com