GrantAccessToMultipleFiles isn't working on Mac M1

Question

I have several VBA macros that run unattended on Windows. I am attempting to port them to the Mac m1 but have run into a problem with sandboxing on the Mac. Everytime the macros attempt to open a new file, it triggers an access permission dialog. Microsoft documentation points to GrantAccessToMultipleFiles as a partial solution.

I wrote the following code to scan a directory and enumerate all the files within to pass to GrantAccessToMultipleFiles. Unfortunately, nothing happens when I pass the files array to the routine nothing happens except that it returns a True indicating access was granted. No permission dialog appears at all.

Later on, when my code attempts to open an enumerated file, I get an access permission request dialog. I thought that perhaps there was an undocumented array limit so passed just a few files but got the same result.

What am I doing wrong?

   Sub directoryGrant(dire)
   Dim files() As String
   Dim i, s, b As Boolean

   i = 0
   s = dir(dire)
   If s = "" Then End
   While s <> ""
     ReDim Preserve files(i)
     files(i) = s
     i = i + 1
     s = dir
   Wend

   b = GrantAccessToMultipleFiles(files)
   If b = False Then i = i / 0
   End Sub 

As I said, the above code, if it worked would only be a partial solution as later on my code downloads client files whose names I don't know until they arrive. I need to be able to open those file without triggering a permission dialog.

Is there any way to disable app sandboxing for Excel on the Mac so I can use my own computer to do my work?

*Edit update. Very early on, I tried "Grant Full Disk Access" to excel which didn't work. Tried GrantAccessToMultipleFiles to just the directory which also didn't work. At that point, I wrote the code above to see if that would work. No joy there either. I finally tried just granting access to a particular file which also didn't work. Either this is an M1 issue, I'm totally messing up or Microsoft never got it to work and released anyway.

Answer 1

I had a similar problem in Word 2019 VBA for Mac. It looks like the argument of GrantAccessToMultipleFiles must be an array, but this array has to contain precisely one element. I had to iterate through a list of files contained in a different array, and to set the content of the array serving as the argument for GrantAccessToMultipleFiles as equal to just one file name on each iteration. The code looks like below

    Dim FileToSplitFullPath As String
    Dim PermissionCandidates
    Dim fileAccessGranted As Boolean
    Dim FileToCombine() As String

    ...
   
    For FileToSplitCount = LBound(FileToCombine) To   UBound(FileToCombine)
        
        FileToSplitFullPath = CurrentPath & FileToCombine(FileToSplitCount)
        PermissionCandidates = Array(FileToSplitFullPath)
        fileAccessGranted = GrantAccessToMultipleFiles(PermissionCandidates)
    
    Next FileToSplitCount

The code above works silently. I fill FileToCombine() from an external file through Split function, but it may be filled in any way you wish. I suppose the code above can be simply every time a file name is created, immediately after it was created and stored somehow for future use. I suppose this is a bug in Mac VBA.

Looking at the code published in thread VBA macro in Excel 2016 for Mac: SaveAs will not work with a CSV file format and in https://answers.microsoft.com/en-us/msoffice/forum/all/vba-macro-in-excel-2016-for-mac-saveas-will-not/86d6855f-241d-4b8d-b233-cccd7184c67b I realized that the issue consisted in the that the argument of GrantAccessToMultipleFiles (1) must be an array, (2) must be declared without type, (3) must contain only one element each time it is used.

Answer 2

If you want to run the macros only on your computer, you may add Excel to the Apps with "Full Disk Access":

• Open System Prefrences
• Click on "Security & Privacy"
• Open the tab "Privacy"
• Scroll down to "Full Disk Access"
• Authenticate using the lock icon lower left
• Add Excel to the list by drag and drop or use the "+" button

You may also try to GrantAccessToMultipleFiles directly to the directory (I'm not fluent in VBA).