CODEWITHSUNDEEP
excelvba
Gaus Shaikh
Gaus Shaikh

Reputation: 209

VBA code to check whether file is read-only

Please advise as to how to check whether a file is read-only before opening it.

Here is the code I am using to open the file:

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

Set ws1 = Worksheets("Students")

Upvotes: 3

Views: 10831

Answers (2)

Carlo Calingasan
Carlo Calingasan

Reputation: 21

if ThisWorkbook.Readonly then Msgbox "Read-Only"

Upvotes: 2

paul bica
paul bica

Reputation: 10715

Ways to check or change attributes:

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

Upvotes: 5

Related Questions