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]

What is your goal? Is the goal just to remove all restrictions on each file?

 

[johnnyL]

If that is your goal, it can be set up to remove all restrictions from a folder and each file/sub folder within that.

You said that you can do it manually file by file right? If that is the case then you should be able to run the code that I am referring to.

 

[gordsky]

Hi Johnnyl, the goal initially is to just get rid of the permissions on the individual files so that past employee access is removed. if that works at a later stage my intention is to set up a permissions list and have each file refer to it for access control rather than on an individual level

 

[johnnyL]

The following code should reset a file:

Sub FilePermission()                                                                        ' From Dos window:  icacls.exe "C:\test\BigFileTestOriginal.xlsm" /reset
'
    Dim FSO                 As Object, objShell         As Object
    Dim PermissionCommand   As String
    Dim PermissionFile      As String, PermissionFolder As String
'
    PermissionFolder = "C:\test\"                                                           ' <--- Set this to the folder that you want
    PermissionFile = "BigFileTestOriginal.xlsm"                                             ' <--- Set this to the filename.ext that you want to change permissions
    PermissionCommand = " /reset"                                                           ' <--- Set this to the command that you want to send
'
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set objShell = CreateObject("Wscript.Shell")
'
    If FSO.FileExists(PermissionFolder & PermissionFile) Then                                       ' If the PermissionFolder & File exists then ...
        objShell.Run "cmd /c icacls.exe " & PermissionFolder & PermissionFile & PermissionCommand   '   Send the command to change permissions
    End If
End Sub

 

[johnnyL]

The following is a way that you can use it like a function:

Sub ResetFilePermission(CurrentFilePath As String, CurrentFileName As String)               ' From Dos window:  icacls.exe "C:\test\BigFileTestOriginal.xlsm" /reset
'
    Dim FSO                 As Object, objShell As Object
    Dim PermissionCommand   As String
'
    PermissionCommand = " /reset"                                                                   ' <--- Set this to the command that you want to send
'
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set objShell = CreateObject("Wscript.Shell")
'
    If FSO.FileExists(CurrentFilePath & CurrentFileName) Then                                       ' If the PermissionFolder & File exists then ...
        objShell.Run "cmd /c icacls.exe " & CurrentFilePath & CurrentFileName & PermissionCommand   '   Send the command to change permissions
    End If
End Sub

And an example code that would utilize that 'Function like' sub:

Sub Test()
'
    Dim PermissionFile  As String, PermissionFolder As String
'
    PermissionFolder = "C:\test\"                                                           ' <--- Set this to the folder that you want
    PermissionFile = "BigFileTestOriginal.xlsm"                                             ' <--- Set this to the filename.ext that you want to change permissions
'
    Call ResetFilePermission(PermissionFolder, PermissionFile)                              ' Call the 'Function like' sub to change permission

End Sub

 

[gordsky]

thanks johnnyL seems to do the job

 

[johnnyL]

Nice! Glad to help.