In Windows Excel 97-2016 and in Mac Excel 2011 you can open files or save files where you want in almost every folder on your system without warnings or requests for permission. But in Mac Office 2016 Microsoft have to deal with Apple’s sandbox requirements. When you use VBA in Mac Excel 2016 that Save or Open files you will notice that it is possible that it will ask you permission to access the file or folder (Grant File Access Prompt), this is because of Apple’s sandbox requirements.This means that when you want to save/open files or check if it exists with VBA code the first time you will be prompted to allow access on the first attempt to access such a folder or file.
There are a few places on your Mac that you can use to avoid the prompts and let your code do what it needs to do without user interaction. But these folders are not in a place that a user can easily find so below are some steps that I hope to make it easier for you to access the folder manual if you want.
This is the Root folder on my machine
that we use in the examples on this page:
Note: rondebruin is the user name in this path and I agree that the naming of the folder for Office(UBF8T346G9.Office) is not so nice, but Microsoft must use that of Apple.
The folder above you can use to share data between Office programs or with a
third party application, so this location will always work if you want to
have read and write access. If you want to have a location only for Excel
for example use this path :
I not use this location on this example page to be sure that every Office program can access my files if this is needed.
For more information and VBA examples to avoid Apple’s sandbox problem read :
Below you find a example workbook that you can use to create a folder if it not exists in the Root folder named : UBF8T346G9.Office
In the macro in the workbook you see one line that call the function and the argument is the name of the folder that you want to create if it not exists. Change "MyProject" to something else to create another folder.
In the other function call to create the shortcut on the desktop you fill in the name of the folder and the name you want for the shortcut.
For the script that add the shortcut to the desktop we must use the new AppleScripTask function because of Apple’s sandbox requirements. The script file must be in the correct location before the VBA code can use it.
Follow the steps below to copy and paste it into this exact location.
Note: More information about this you find on the AppleScriptTask page on my site
If you have add one or both folders and have problems with the code on this page reboot your Mac first and test it again.
If you want to use the example in Word you must add/use the com.microsoft.Word folder, each Office app have its own folder. Unfortunately there is no folder for all Office programs.