Make folder and Shortcut | Mac Excel Automation

Create 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

Note: This example can be useful to create a folder for your Excel files in a location with no sandbox problems and have the link to it on your desktop.

With the AppleScriptTask method that I use on this page you need to distribute an extra file containing the script, and it must be placed in the specified location on the user’s system to have permission to run. This requires some user interaction the first time.

I create this install script for you to copy the script file in the correct location, you can download the Install.scpt script together with the CreateFolderAndShortcutOnDesktop.scpt file below. The folder must be in the Users Download folder, you see that if you download the compressed folder from my website it will automatically uncompressed in your Downloads folder and you can double-click the Install.scpt file in it to Open and Run it to copy the CreateFolderAndShortcutOnDesktop.scpt in the correct location so the AppleScriptTask function can find it.

Download the Install.scpt and CreateFolderAndShortcutOnDesktop.scpt files below
Download Files. (File dates 8-Dec-24)

Below you find the VBA macro and custom function that you can use in your VBA projects

Note: When you run the macro below it is possible it will ask you permission to create the link on the Desktop (System Events.app).

Sub TestCreateFolderAndLink()
'Create folder if it not exists in the Microsoft Office Folder
'And also create a shortcut to it on the Desktop
'Note: this macro use the custom MakeFolderShortcutOnDesktop function
'First argument is the Folder name en the second the link name on your Desktop
'You can also use this MacOfficeFiles/MyExcelProject for a folder inside another folder

Call MakeFolderShortcutOnDesktop("MyExcelProject", "Link To MyExcelProject")
End Sub


Function MakeFolderShortcutOnDesktop(NameFolder As String, NameFolderLink As String)
    'Function to create folder if it not exists in the Microsoft Office Folder
    'And also create a shortcut to it on the Desktop
    'https://macexcel.com/examples/filesandfolders/makefolderandshortcut/
    'Ron de Bruin : 8-Dec-2024
    Dim AppleScriptTaskDesktopPath As String
    Dim AppleScriptTaskFilePath As String
    Dim RunMyScript As Boolean
    Dim RunMyScript2 As Boolean
    Dim RunMyScript3 As String
    Dim OfficeFolder As String
    Dim PathToLink As String
    Dim ScriptStr As String

    'Check if the AppleScriptTask file is in the correct location
    AppleScriptTaskDesktopPath = MacScript("return POSIX path of (path to desktop folder) as string")
    AppleScriptTaskFilePath = Replace(AppleScriptTaskDesktopPath, "/Desktop", "") & _
        "Library/Application Scripts/com.microsoft.Excel/CreateFolderAndShortcutOnDesktop.scpt"

    RunMyScript = AppleScriptTask("CreateFolderAndShortcutOnDesktop.scpt", "ExistsFile", AppleScriptTaskFilePath)
    
    If RunMyScript = False Then
        MsgBox "Sorry the CreateFolderAndShortcutOnDesktop.scpt file is not in the correct location"
        Exit Function
    Else
        'Create the folder if not exists
        OfficeFolder = Replace(AppleScriptTaskDesktopPath, "/Desktop", "") & _
        "Library/Group Containers/UBF8T346G9.Office/" & NameFolder
        
        RunMyScript2 = AppleScriptTask("CreateFolderAndShortcutOnDesktop.scpt", "ExistsFolder", OfficeFolder)
       
        If RunMyScript2 = False Then
            MkDir OfficeFolder
            PathToLink = AppleScriptTaskDesktopPath & NameFolderLink
            ScriptStr = PathToLink & ";" & OfficeFolder
            DoEvents
            RunMyScript3 = AppleScriptTask("CreateFolderAndShortCutOnDesktop.scpt", "CreateShortcutOnDesktop", CStr(ScriptStr))
        Else
            MsgBox "The folder already exists"
        End If
    End If

End Function