2

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")
Kenny83
  • 769
  • 12
  • 38
Gaus Shaikh
  • 189
  • 2
  • 8
  • 18
  • 3
    I don't know the answer, but I did a little playing. Maybe this information will help someone else figure out the answer. I saved a book as read-only, then tried to use `FSO` to check the `file.attributes`. It showed as [32](http://www.4guysfromrolla.com/webtech/112600-1.shtml) meaning not `ReadOnly`. Opening it prompted me to open it `ReadOnly`. Changing the file attribute to Read only made `FSO` show it as 33, and I did NOT get prompted to open it read only. The `ReadOnly` flag in the `Open` method is ignored unless its set to `TRUE`. Not sure there's a way to check BEFORE the wb opens. :/ – Tim Aug 07 '15 at 19:34
  • 2
    Same problem here. **For those who want to try it**: You need to refer the Microsoft Scripting Runtime. **Remark** 33 = 32 + 1, meaning _to Archive_ and _ReadOnly_. Using the bitwise `And` operator, you can write `isReadOnly = file.Attributes And 1` **Perhaps** [This post](http://stackoverflow.com/questions/5651890/using-vba-to-get-extended-file-attributes) might help, but I could not get the Shell object. – Dirk Horsten Aug 07 '15 at 19:45
  • This might help too: http://www.ozgrid.com/forum/showthread.php?t=48890&s=7e7b6a75b9d920235ef3f5dcfcf79b6e&p=249171#post249171 – NickSlash Aug 07 '15 at 22:13

2 Answers2

4

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
paul bica
  • 10,557
  • 4
  • 23
  • 42
  • That is a different question but, to unprotect a workbook go to the Review tab, last section on the right (Changes) and click on Protect Workbook - that's where you put the password. Please close this question if the answer provided addresses your initial problem – paul bica Aug 08 '15 at 12:54
2

if ThisWorkbook.Readonly then Msgbox "Read-Only"

Carlo Calingasan
  • 21
  • 1
  • 1
    Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Sep 14 '22 at 16:23