vba 检查是否打开了只读工作簿 [英] vba check if a read-only workbook is opened

问题描述

我似乎无法获得有关是否打开只读工作簿的明确反馈.在我的代码中，我在关闭/保存工作簿后复制了它.如果其他用户以只读方式打开只读工作簿，我希望能够覆盖它.我尝试过类似这样的代码，但没有运气，它只是一直说文件未打开！"即使我打开了它.如何检查是否在 vba 中打开了只读 .xlsx"文件?

I cannot seem to get a definite feedback on whether or not a read-only workbook is opened or not. In my code I have it copy after closing/saving the workbook. I would like to be able to overwrite the read-only workbook if it's opened as read-only by another user. I tried this something like this bit of code, but had no luck, it just kept saying "File not open!" even when I had it opened. How can I check whether or not a "read-only .xlsx" file is opened or not in vba?

Sub Test_If_File_Is_Open_2()

Dim wBook As Workbook

On Error Resume Next

Set wBook = Workbooks("C:\Users\" & Environ("username") & "\Documents\Dropbox\Systems\Open     Machine Schedule\Open Machine Schedule.xlsx")
If wBook Is Nothing Then 'Not open
MsgBox "File is Not open!"
Else 'It is open
MsgBox "File is Open!" 'Never get this to display when I have the excel file open

End If

End Sub

提示使用这段代码(上面)是因为我希望宏在只读工作簿被另一个用户打开时不会导致错误.当我运行下面的宏并事先打开复制的只读工作簿时，出现错误:vba 运行时错误 1004 无法访问只读文档"当复制的工作簿关闭时，我没有收到此错误，它会像预期的那样覆盖它.这是提示这个问题的代码:

What started the prompt for using this bit of code(above) was because I wanted the macro to not cause an error if the read-only workbook was opened by another user. When I run the macro below and have the copied read-only workbook opened prior, I get an error:"vba run time error 1004 cannot access read-only document" I don't get this error when the copied workbook is closed, it overwrites it like it's supposed to. Here is the code that prompted this question:

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim backupfolder As String

backupfolder = "C:\Users\" & Environ("username") & "\Documents\Dropbox\Systems\Open Machine Schedule\"

ThisWorkbook.SaveAs Filename:=backupfolder & "Open Machine Schedule - Current.xlsx", FileFormat:=xlOpenXMLWorkbook
End Sub

Sub Auto_Save()

Dim savedate

savedate = Date

Dim savetime
savetime = Time
Dim formattime As String
formattime = Format(savetime, "hh.MM.ss")
Dim formatdate As String
formatdate = Format(savedate, "DD - MM - YYYY")


Application.DisplayAlerts = False

Dim backupfolder As String
backupfolder = "C:\Users\" & Environ("username") & "\Documents\Dropbox\Systems\Open Machine Schedule\"
ActiveWorkbook.Save
ActiveWorkbook.SaveAs backupfolder & "Open Machine Schedule - Current.xlsx", FileFormat:=xlOpenXMLWorkbook
SetAttr backupfolder & "Open Machine Schedule - Current.xlsx", vbReadOnly
Application.DisplayAlerts = True
MsgBox "Backup Run. Please Check at: " & backupfolder & " !"

End Sub

任何帮助/建议将不胜感激

Any help/suggestions would be much appreciated

推荐答案

你的第一个代码只是测试工作簿是否存在，而不是它的状态.

your first code is only testing to see if a workbook exist not its state.

你可以改用这个:

If wBook.ReadOnly Then
    MsgBox "File is Read-only"
Else
    MsgBox "File is not read-only"
End If

这篇关于vba 检查是否打开了只读工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！