VBA code for file access settings

[gordsky]

Hi All,

Not sure if his is even possible but looking for a vba code to replicate the following.......

I have a large number of files which had permissions set through "right click, properties, security. edit (etc)" so that only the individual and their line manager can access the file from explorer. Unfortunately the person who initiated this didn't seem to anticipate staff changes in the company. this means now the only way to make a change when someone leaves is to manually change each file. Is there a way to bulk change these through VBA.

I know how to loop through a folder and access files individually through VBA but wouldn't have a clue on how or if it is possible to change the permissions.
Any help would be appreciated.

 

[johnnyL]

How about something like:

    Dim myPermission As Office.Permission
    Set myPermission = ActiveWorkbook.Permission
'
' Remove all UserPermissions that have been added to the Permission collection and disable restrictions on the active document
    myPermission.RemoveAll
    Set myPermission = Nothing

 

[gordsky]

How about something like:

    Dim myPermission As Office.Permission
    Set myPermission = ActiveWorkbook.Permission
'
' Remove all UserPermissions that have been added to the Permission collection and disable restrictions on the active document
    myPermission.RemoveAll
    Set myPermission = Nothing

JohnnyL thanks for the reply, much appreciated, can you expand on what the code does.? Does it remove permissions for everyone or does it reset the permissions to all have access?

 

[johnnyL]

If I read it correctly, it will remove all previously added users from the Permissions 'list' and set the file to a state of as if it were never restricted in any way. In other words, everyone will have full access to it.

 

[gordsky]

If I read it correctly, it will remove all previously added users from the Permissions 'list' and set the file to a state of as if it were never restricted in any way. In other words, everyone will have full access to it.

Thanks for the explanation. Tried the code this morning on a test file but it didn't work. By that I mean it ran fine with no errors but did nothing to the permissions. They remained as they were before running. Any ideas?

 

[johnnyL]

What happens when you run the following:

Sub CheckUsers()
    Dim myPermission As Office.Permission, UsersPermission As Office.UserPermission
    Set myPermission = ActiveWorkbook.Permission
'
    myPermission.RemoveAll
'
    If myPermission.Enabled Then
        For Each UsersPermission In myPermission
            strIRMInfo = strIRMInfo & UsersPermission.UserId & vbCrLf & " - Permissions: " & UsersPermission.Permission & vbCrLf & _
                    " - Expiration Date: " & UsersPermission.ExpirationDate & vbCrLf
        Next
'
        MsgBox strIRMInfo, vbInformation + vbOKOnly, "IRM Information"
    Else
        MsgBox "This document is not restricted, Or you don't have authorization.", vbInformation + vbOKOnly, "IRM Information"
    End If
'
    Set UsersPermission = Nothing
    Set myPermission = Nothing
End Sub

 

[John_w]

I have a large number of files which had permissions set through "right click, properties, security. edit (etc)" so that only the individual and their line manager can access the file from explorer. Unfortunately the person who initiated this didn't seem to anticipate staff changes in the company. this means now the only way to make a change when someone leaves is to manually change each file. Is there a way to bulk change these through VBA.

You can change file permissions using the DOS ICACLS command from a command window:

icacls

Reference article for the icacls command, which displays or modifies discretionary access control lists (DACL) on specified files, and applies stored DACLs to files in specified directories.

docs.microsoft.com

You don't really need VBA, but if you wish ICACLS commands can be run using the VBA Shell function or the WScript.Shell object.

 

[gordsky]

What happens when you run the following:

Sub CheckUsers()
    Dim myPermission As Office.Permission, UsersPermission As Office.UserPermission
    Set myPermission = ActiveWorkbook.Permission
'
    myPermission.RemoveAll
'
    If myPermission.Enabled Then
        For Each UsersPermission In myPermission
            strIRMInfo = strIRMInfo & UsersPermission.UserId & vbCrLf & " - Permissions: " & UsersPermission.Permission & vbCrLf & _
                    " - Expiration Date: " & UsersPermission.ExpirationDate & vbCrLf
        Next
'
        MsgBox strIRMInfo, vbInformation + vbOKOnly, "IRM Information"
    Else
        MsgBox "This document is not restricted, Or you don't have authorization.", vbInformation + vbOKOnly, "IRM Information"
    End If
'
    Set UsersPermission = Nothing
    Set myPermission = Nothing
End Sub

It defaults to the not restricted / dont have authorization message

 

[johnnyL]

Ok it sounds like you are not dealing with IRM, so that approch is not going to work.

The method @John_w suggested is probably what you need.

 

[gordsky]

Ok it sounds like you are not dealing with IRM, so that approch is not going to work.

The method @John_w suggested is probably what you need.

thanks for your time on this one. Ill give that a try but suspect I wont have sufficient access rights to be able to run something at commandline level