excel - VB Macros for Office 2016 for Mac require Permissions every time they try to access a file! Is there any way to get around this behavior?

Question

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.

Answer 1

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