Make folder in the Office folder and shortcut on the Desktop

Note : When you use VBA in Mac Excel 2016 or higher that is working with files and folders 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. If you want to avoid problems like this read this page : Problems with Apple’s sandbox requirements

Create Folder in the Office folder and shortcut with VBA code

Below you download an example workbook and script file that you can use to create a folder if it not exists in the Root folder named : UBF8T346G9.Office and a shortcut to it on the Desktop.

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 that 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 AppleScriptTask 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.
  • Open a Finder Window
  • Hold the Alt key and click Go in the Finder menu bar
  • Click Library
  • Click Application Scripts (if it exists; if not create this folder)
  • Click com.microsoft.Excel if it exists; if not create this folder (note: Capital letter E)
  • Copy CreateFolderShortcutOnDesktop.scpt to the com.microsoft.Excel folder.

Check out this page if you want to know more about AppleScriptTask :
How to use AppleScriptTask in Mac Office

Download Example workbook and Script file

31-July-2021
Web design by Will Woodgate