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.