如果以只读方式打开，请停止宏并关闭工作簿？

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

1 个解决方案

#1

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

您可以使用属性Workbook.ReadOnly来测试工作簿是否为只读。

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

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

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

#1