excel - 检查文件是否只读的VBA代码

Question

请告知如何在打开文件之前检查文件是否为只读。

这是我用来打开文件的代码:

myFileNameDir = "H:\Shaikh_Gaus\scratch\VBA\Book16.xlsx"
Workbooks.Open Filename:=myFileNameDir, UpdateLinks:=0

Set ws1 = Worksheets("Students")

Answer 1

检查或更改属性的方法:

Option Explicit

Sub testFileAttributes()

    Const FILE_NAME As String = "C:\Test.txt"

    If isReadOnly(FILE_NAME) Then MsgBox "File is Read-only"

    If isOpenAsReadOnly Then MsgBox "File is open as Read-only"

    makeReadWrite FILE_NAME

    If Not isReadOnly(FILE_NAME) Then MsgBox "File is not Read-only"

End Sub

---

.

Public Function isReadOnly(ByVal fName As String) As Boolean

    'vbNormal = 0, vbReadOnly = 1, vbHidden = 2, vbDirectory = 16

    if Len(fName) > 0 Then isReadOnly = GetAttr(fName) And vbReadOnly

End Function

.

Public Function isOpenAsReadOnly(Optional ByRef wb As Workbook = Nothing) As Boolean

    If wb Is Nothing Then Set wb = ActiveWorkbook

    isOpenAsReadOnly = wb.ReadOnly 'opened as read-only within Microsoft Excel

End Function

.

---

Public Sub makeReadWrite(ByVal fName As String)

    Const READ_ONLY = 1

    Dim fso As Object, fsoFile As Object

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fsoFile = fso.getFile(fName)

    With fsoFile
        If .Attributes And READ_ONLY Then .Attributes = .Attributes Xor READ_ONLY
    End With
End Sub