Hello,

I am using Excel for Mac and have written a macro that goes into 51 different Excel files, all stored on Dropbox, copies a range, pastes it into a single location and closes the files without saving.
The macro works fine but I get a message asking me to "Grant File Access". I then manually have to click "grant access".

I understand that this occurs because Office for Mac apps are sandboxed. I also understand that once permission has been granted, this is stored and should not need to be given again, however, this is not the case - I continue to be asked to grant access to files. It does not ask me to grant access for every file each time I run the macro but it does ask for access to a handful of files every 4-5 times the macro is run (sometimes more frequently).

Is there anyway I can build in a step in the macro that automatically grants access so I can leave the macro running without manual input?


I have seen the below offered as a solution... 

Sub requestFileAccess()  

    'Declare Variables  
    Dim fileAccessGranted As Boolean  
    Dim filePermissionCandidates 

    'Create an array with file paths for the permissions that are needed.  
    filePermissionCandidates = Array("/Users//Desktop/test1.txt", "/Users//Desktop/test2.txt") 

    'Request access from user.  
    fileAccessGranted = GrantAccessToMultipleFiles(filePermissionCandidates) 
    'Returns true if access is granted; otherwise, false. 
End Sub
...however, this would require me to list the 51 file names in the macro (?). Currently my code is written as below with a list of the 51 file names held in excel which then get used in a loop.

    Dim Filepath As String
    Dim FILEname As Range
    Sheets("FILEList").Select
    For Each FILEname In Range("D3:D45")
    Filepath = "/Users/" & GetUserNameMac & "/Dropbox/" & FILEname & "/" & FILEname & ".xlsx"
        Workbooks.Open FILEname:=Filepath, UpdateLinks:=0

Any help is appreciated!




(I am aware the we are not supposed to "inquire about breaking the security of a protected workbook". As I understand it, my question does not break this forum rule, apologies if it does!)