CODEWITHSUNDEEP
vbaexcel
Dennis
Dennis

Reputation: 91

Stop Macro and close workbook if opened as read only?

I've worked out a macro that will open a couple of other workbooks, refresh the data and then save and close. However, the books being opened may sometimes be in use by another department if they are adding data. Is there a way to have the macro terminate and close the workbook if it is opened as read only?

The problem is that the macro encounters a read only error when trying to save.

Sub Refresh_All()
'
' Refresh_All Macro
'
' Keyboard Shortcut: Ctrl+Y
'
    ChDir "Q:\Quality Control"
    Workbooks.Open Filename:= _
        "Q:\Quality Control\Internal Failure Log - Variable Month.xlsm"
    Dim endTime As Date
    endTime = DateAdd("s", 10, Now())
    Do While Now() < endTime
DoEvents
    Loop
    ActiveWorkbook.RefreshAll
       endTime = DateAdd("s", 10, Now())
    Do While Now() < endTime
        DoEvents
    Loop
ActiveWorkbook.Save
    endTime = DateAdd("s", 5, Now())
    Do While Now() < endTime
        DoEvents
    Loop
    ActiveWindow.Close
    ChDir "Q:\Reports"
    Workbooks.Open Filename:= _
        "Q:\Reports\Finished-Transfer Report-variable month.xlsm"
        endTime = DateAdd("s", 10, Now())
    Do While Now() < endTime
        DoEvents
    Loop
ActiveWorkbook.RefreshAll
     endTime = DateAdd("s", 10, Now())
    Do While Now() < endTime
        DoEvents
    Loop
ActiveWorkbook.Save
        endTime = DateAdd("s", 5, Now())
    Do While Now() < endTime
        DoEvents
    Loop
ActiveWindow.Close
    ActiveWorkbook.RefreshAll
        endTime = DateAdd("s", 10, Now())
    Do While Now() < endTime
        DoEvents
    Loop
ActiveWorkbook.Save
End Sub

Upvotes: 0

Views: 2651

Answers (1)

ryankey721
ryankey721

Reputation: 53

You can use the property Workbook.ReadOnly to test if the workbook is read only.

If ActiveWorkbook.ReadOnly Then
   <your code here>
End If

Workbook.ReadOnly description: https://msdn.microsoft.com/en-us/library/office/ff840925.aspx

Upvotes: 3

Related Questions