7

Any VB Macro in Office 2016 shows a dialog box to the user asking for permission, every time the Macro tries to access a file! Is there a way to avoid it.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
ramitarora
  • 421
  • 2
  • 4
  • 10

2 Answers2

12

Unlike VB Macros in Office for Mac 2011, VB Macros in Office 2016 for Mac do not have access to external files by default. The Office 2016 for Mac apps are sandboxed and hence they lack the required permissions to access external files.

Existing macro file commands are changed to prompt the user for file access if the app doesn’t already have access to it. This means that macros that access external files cannot run unattended; they will require user interaction to approve file access the first time each file is referenced.

Developers should use the GrantAccessToMultipleFiles command (see following section) to avoid this experience. This command lets your app get permission for all the files at one time, thereby avoiding a difficult user experience.

GrantAccessToMultipleFiles
This lets you input an array of file paths and prompt the user for permission to access them.

Boolean  GrantAccessToMultipleFiles(fileArray)

  • Parameters

    • fileArray -- An array of POSIX file paths.

  • Return Values

    • True - The user grants permission to the files.
    • False - The user denies permission to the files.


Note: Once granted, the permissions are stored with the app and user need not grant permission to the file anymore.

Example:    

Sub requestFileAccess()  
  
'Declare Variables  
    Dim fileAccessGranted As Boolean  
    Dim filePermissionCandidates 
  
 'Create an array with file paths for which permissions are needed  
    filePermissionCandidates = Array("/Users/<user>/Desktop/test1.txt", "/Users/<user>/Desktop/test2.txt") 
  
'Request Access from User  
    fileAccessGranted = GrantAccessToMultipleFiles(filePermissionCandidates) 'returns true if access granted, false otherwise  
      
  
End Sub
ramitarora
  • 421
  • 2
  • 4
  • 10
  • WAIT! Apps for Office are not the same thing as VBA Add-Ins, despite Microsoft's recent attempts to confuse the two. VBA has not required permissions to open files, either in Windows through Office 2016 or in Mac Office 2011. VBA Add-Ins have never had to be distributed through the Office store or the Apple store or any other intrusive means. – Jon Peltier Jun 22 '15 at 15:31
  • "Once granted, the permissions are stored with the app" And what is it we're calling the app? The add-in? How is the permission stored, if the add-in can be stored in an arbitrary directory? – Jon Peltier Jul 06 '15 at 19:35
  • 1
    @JonPeltier Adding this late, I know, but in case it helps: according to one of the Mac Office devs, from the POV of the Apple sandbox, add-ins are part of the Office programs they're installed in. Whether Excel or an Excel add-in is doing something, the same rules apply. – Steve Rindsberg Sep 20 '17 at 15:00
  • Works like charm! almost struggled for two days to get this working – Madan Apr 08 '21 at 13:15
  • @ramitarora The logic seems to be good, but what if you have to grant permission to 1000 files in a folder? It seems ridiculous to enter the paths of all the files inside an array. Is there a turnaround for this?? – Vivek Apr 13 '21 at 01:51
  • @vivek Excel might block it, but possibly you could enumerate the files in a folder using the filesystem object, and then feed in the array of names – Ben McIntyre Nov 09 '21 at 01:23
  • Works with Excel (Office 365) on a 2020 M1 MacAir, for granting access to both files and subfolders. After modifying the sub to make filePermissionCandidates a sub parameter (variant) and passing the full file paths as a string array, it asked just once to grant permissions and then processed the 250+ files and subfolders in that folder. Thanks! – Tony M Feb 19 '22 at 22:50
2

I wanted to delete a file in Excel using VBA's Kill syntax and got the permission prompt when the VBA attempted to execute the command. My script is supposed to run once every 3 minutes day and night so responding to separate prompts wouldn't cut it.

To workaround the issue, I created a wrapper for VBA's Kill function called myKill. To implement it I:

  1. Globally replaced all calls to Kill with myKill in my VBA code.

  2. Added a new VBA subroutine called myKill (code below)

  3. Created an AppleScript file to actually remove the file (code below)

  4. Granted Excel Full Disk access in System Preferences/Security & Privacy/Privacy. Not sure this step was necessary but I mention it as it's my current configuration. It's worthless without the first three steps.

Here is the myKill subroutine:

 Sub myKill(fname As String)

 Dim s As String

 If Left(Application.OperatingSystem, 3) = "Mac" Then
     s = AppleScriptTask("Kill.applescript", "Kill", fname)
 Else
     Kill fname
 End If

End Sub

Here is the Kill.applescript file contents:

on Kill(fileName)
# implementation of vba kill command on Mac. Mac sandboxes Microsoft's
# kill command 

tell application "System Events" to delete alias fileName

end Kill

The Kill.applescript file must be stored in

 /Users/your user name/Library/Application Scripts/com.microsoft.excel/Kill.applescript

The first time the Apple Script executes, Mac OS will ask for permission for Excel to manipulate files in the directory where the file to be deleted is located. Once given, future file deletes in the directory won't trigger the permission request. I tested that by exiting Excel, reentering and deleting another file.

RANT How anyone thought making programmers jump through all these hoops or click "OK" each time a file had to be deleted was an acceptable implementation is beyond me. Because someone in Microsoft dropped the ball on abstracting simple file manipulations like Kill over 10,000 programmers have seen this web page trying to figure out what they have to do to delete a file in Microsoft Excel.

Yo Microsoft VBA Project Manager! I'm talking to you! Your job exists because Joel Spolsky convinced Bill Gates that a macro language for Excel was a good idea despite Gates' doubts. Your job is to ensure that Excel VBA abstracts os weirdness so programmers can get their job done and not have to hack in another language.

Michael
  • 726
  • 7
  • 18