Check if a file is write protecteb before opening it

[Silverryggen]

Hi,

I am trying to create a function in VBA that checks if the target file is write protected before opening it. I have tried the code below without any luck

Filename is a variable passed in from the main sub.

Hopefully someone knows the answer to this problem

Function Protected (Filename) As Boolean
Dim test As Boolean

On Error Resume Next
Application.DisplayAlerts = False

If Workbooks(Filename).ReadOnly Then
Protected = True
Else
Protected = False
End If

Application.DisplayAlerts = True
End Function

 

[Richard Schollar]

Hi

Try this:

Function IsFileWriteable(strFile As String) As Boolean
Dim i As Integer
i = FreeFile
On Error Resume Next
Open strFile For Binary Access Write As #i
Close #i
IsFileWriteable = (Err = 0)
End Function

 

[jim may]

Richard,
Does strFile have to be an OPEN file?
Is there a tutorial on what each line of your code means/does?
Jim May

 

[Richard Schollar]

Jim

strFile is just the full name of a file (presumably closed, as the OP wanted to check before it was opened) eg like "C:\Somefolder\Book1.xls".

The code otherwise just uses standard VBA functions. The important bit is that it attempts to open the file (using the Open statement) for write access. If it can't then the file is read-only, if it can then it is writeable.

 

[jim may]

Testing this a bit.. leads to the following q..

If the Function returns TRUE - then the Workbook is Unprotected?

I created a New WB - and Simply did a Tools, Protection, Workbook. checked both Structure and Windows; applied Password and Closed Filename c:\Myfolder\Test.xls

In my WB where I have your Function Code
I added the code line Application.Volatile
and in cell K1 I entered =Today()
I enter into a cell (A1):
=IsFileWriteable("c:\Myfolder\Test.xls")

Currently the formula is TRUE << seems like it should be FALSE (since the WB is protected).. Confused here,, can you assist?

Jim

 

[Richard Schollar]

The function checks if a file is write-protected (as per OP's original request) - it doesn't check if a file has workbook/sheet protection.

 

[jim may]

Since He asked "if the target file is write protected" -- I guess that means
If the file is READ-ONLY... OK, I now see it.
But it brings up an interesting Q
Can a function (udf) dertermine if an Unopened file is WB Protected?
Thanks,
Jim

 

[Silverryggen]

Thx a lot for the code, it works really nice