The MacScript function is not working well in Office for Mac 2016! Any ideas?

My macros use MacScript heavily, but it doesn't seem to be working in any of latest Office for Mac 2016 preview builds

Solution 1:

The MacScript command, used to support inline Apple Scripts in Office for Mac 2011, is being deprecated. Due to restrictions of the sandbox, the MacScript command can no longer invoke other applications such as the Finder. Therefore we discourage the use of this command.

For cases which require changing your existing code so it doesn’t use MacScript, you can use the AppleScriptTask command (see below).

The new AppleScriptTask command executes an AppleScript script. This similar to the MacScript command except that it runs an AppleScript file located outside the sandboxed app. Call AppleScriptTask as follows:

 Dim myScriptResult as String

 myScriptResult = AppleScriptTask ("MyAppleScriptFile.applescript", "myapplescripthandler", "my parameter string") 

Where:

• The “MyAppleScript.applescript” file must be in ~/Library/Application Scripts/[bundle id]/, the extension applescript is not mandatory, .scpt may also be used
• “myapplescripthandler” is the name of a script handler in the MyAppleScript.applescript file
• “my parameter string” is the single input parameter to the “myapplescripthandler” script handler.
• The corresponding AppleScript for Excel would be in a file named "MyAppleScriptFile.applescript" that is in ~/Library/Application Scripts/com.microsoft.Excel/

Note: The [bundle id]s for Mac Word, Excel and PowerPoint are:

• com.microsoft.Word
• com.microsoft.Excel
• com.microsoft.Powerpoint

An example of a handler is as follows:

on myapplescripthandler(paramString) 

    #do something with paramString 
    return "You told me " & paramString 

end myapplescripthandler

Solution 2:

Perhaps the most frustrating part of the lack of support is the "workaround" of creating the folder structure and script files.

To solve this problem, I created an AppleScript that runs like an installer to set up the AppleScript folder and files that you need to pass along with your VBA application for AppleScriptTask to work. I utilized the "FileExists" and "FolderExists" examples from Ron De Bruin's website (http://www.rondebruin.nl/mac/applescripttask.htm). Those two functions are below and are used to determine if a File or Folder Exists:

on ExistsFile(filePath)
    tell application "System Events" to return (exists disk item filePath) and class of disk item filePath = file
end ExistsFile
on ExistsFolder(folderPath)
    tell application "System Events" to return (exists disk item folderPath) and class of disk item folderPath = folder
end ExistsFolder

You can run the below script by saving it to a file called "InstallFileFolderScript.scpt". It does two things:

1. Creates the folder structure for Office 2016 for Mac MS Word scripts: "~/Library/Application Scripts/com.microsoft.Word".
2. Creates a script file containing two functions "FileExists" and "FolderExists" in the current working directory.
3. Copies the script file into the com.microsoft.Word folder.
4. Deletes the temp script from the working directory after the file copy.

Feel free to modify it to add additional functions as needed for the application. Each line of the script file is written using this script. It can also be modified to work with Excel and other office Apps:

property theFolders : {"~/Library/'Application Scripts'/com.microsoft.Word"}

try
    tell application "Finder" to set targetFolder to (target of the front window) as alias
on error -- no window
    set targetFolder to (choose folder)
end try

# build a parameter string from the folder list
set {tempTID, AppleScript's text item delimiters} to {AppleScript's text item delimiters, space}
set {theFolders, AppleScript's text item delimiters} to {theFolders as text, tempTID}

do shell script "cd " & quoted form of POSIX path of targetFolder & "; mkdir -p " & theFolders

--Write the Script file if it does not exist
if ExistsFile("~/Library/'Application Scripts'/com.microsoft.Word/FileFolderScript.scpt") is false then
    tell application "Finder"
        --GET THE WORKING DIRECTORY FOR FILE COPY OF SCRIPT
        get folder of (path to me) as Unicode text
        set workingDir to POSIX path of result

        --Write the new script in the current working directory
        set textFile to workingDir & "FileFolderScript.scpt"

        --Delete script if it exists
        set posixPath to POSIX path of textFile as string
        do shell script "rm -rf \"" & posixPath & "\""

        --Create File and Folder Script Interface for Microsoft Word VBA Applications
        set fd to open for access textFile with write permission
        write "on ExistsFile(filePath)" & linefeed to fd as «class utf8» starting at eof
        write "tell application \"System Events\" to return (exists disk item filePath) and class of disk item filePath = file" & linefeed to fd as «class utf8» starting at eof
        write "end ExistsFile" & linefeed to fd as «class utf8» starting at eof
        write "on ExistsFolder(folderPath)" & linefeed to fd as «class utf8» starting at eof
        write "tell application \"System Events\" to return (exists disk item folderPath) and class of disk item folderPath = folder" & linefeed to fd as «class utf8» starting at eof
        write "end ExistsFolder" & linefeed to fd as «class utf8» starting at eof
        close access fd

        --Copy the script file into the MACOS-Specific 'safe' folder
        set fromPath to quoted form of POSIX path of (workingDir) & "FileFolderScript.scpt"
        set toPath to quoted form of "~/Library/'Application Scripts'/com.microsoft.Word"
        do shell script "cp -R " & fromPath & space & "~/Library/'Application Scripts'/com.microsoft.Word" with administrator privileges
    end tell
end if

--Delete the temp script file from the working directory
set posixPath to POSIX path of textFile as string
do shell script "rm -rf \"" & posixPath & "\""

--Provide confirmation
display dialog "The File and Folder script necessary for Mac OS and Microsoft Office 2016 VBA integration has been successfully installed."

--For use when checking if a file exists
on ExistsFile(filePath)
    tell application "System Events" to return (exists disk item filePath) and class of disk item filePath = file
end ExistsFile

Finally, within the VBA application I use this to call the AppleScript functions:

Function Mac2016_FileOrFolderExists(FileOrFolder As Long, FilePathName As String)
    Dim RunMyScript As Boolean

    If (FileOrFolder = 1) Then
        RunMyScript = AppleScriptTask("FileFolderScript.scpt", "ExistsFile", FilePathName)
    Else
        RunMyScript = AppleScriptTask("FileFolderScript.scpt", "ExistsFolder", FilePathName)
    End If

    Mac2016_FileExists = RunMyScript
End Function

I also found this Microsoft article very helpful and easy to understand: https://dev.office.com/blogs/VBA-improvements-in-Office-2016. It details usage of AppleScriptTask, and also covers the folder permissions workaround that you commonly will have to implement alongside AppleScriptTask when working with files/folders.