Microsoft Excel

Ron de Bruin
Excel Automation

Microsoft MVP Program

Make folder in the Office folder in Office 2016 and create shortcut on the Desktop with VBA

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.

How to avoid problems

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:
/Users/rondebruin/Library/Group Containers/UBF8T346G9.Office
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 : /Users/rondebruin/Library/Containers/com.microsoft.Excel/Data
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 :

Problems with Apple’s sandbox requirements and Mac Office 2016 with VBA code

 

Create Folder in the Office folder and shortcut with VBA code

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.

Where to place the script file for using it with AppleScriptTask

 Follow the steps below to copy and paste it into this exact location.

  1. Open a Finder Window
  2. Hold the Alt key and click Go in the Finder menu bar
  3. Click Library
  4. Click Application Scripts (if it exists; if not create this folder)
  5. Click com.microsoft.Excel if it exists; if not create this folder (note: Capital letter E)
  6. Copy CreateFolderShortcutOnDesktop.scpt to the com.microsoft.Excel folder.

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.

 

Download Example workbook and script file

Download example workbook together with the AppleScripTask file