AppleScriptTask | Mac Excel Automation

How to use AppleScriptTask in Mac Office

In Mac Office there are many problems with the built-in VBA commands. VBA developers responded by using AppleScript in many situations to work around the problems. They also used AppleScript to do things that are not possible with VBA code; for example to email using VBA code.

In Office 2016 and higher, we need to use a new method and a new approach explained below.

Example used in Mac Excel 2011

In Office 2011 we use the built-in MacScript function to run a script that we build up as a string in the VBA code. See the code example below. Copy the test macro and the function below into a normal module of your workbook. Change the file path and name in the macro TestMacro to point to a file on your Mac to test.

When you run the macro named TestMacro in Excel 2011 it will test if the file :
Macintosh HD:Users:RDB:Desktop:MacTestFile.xlsm
exists on your Mac and display a msgbox showing True or False

Note: You can also use : FileName = "/Users/RDB/Desktop/MacTestFile.xlsm"

Mac Excel 2016 or higher

If you test the code that is working correctly in Excel 2011 in Excel 2016 and higher it gives a run-time error 5; but if you test the script string that the VBA code created in the Script Editor, the script works correctly. Note: you can use VBA Dir also now to test if a file exists on your Mac because they fixed the problems that exists with Dir, this was not fixed when I create this page.

What’s the problem here?

The legacy "MacScript" VBA Command is severely limited by Apple’s sandbox requirements: it will not work correctly in most situations in Office 2016 and higher.

Instead, Microsoft added a new VB command "AppleScriptTask" that accesses and runs an AppleScript file located outside the sandboxed app. This new approach is not as convenient: with the MacScript function you could have the script in the file itself, while with the AppleScriptTask method 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.

1) Test the AppleScript in the Script Editor

First we open the Script Editor on your Mac.

Click on the Spotlight icon in the top right corner of your Mac.
Enter "Script Editor" to find and open this program.
Click the New Document button, and we are ready to start.

Tip: Right click on the Script Editor icon in the dock and choose Options>Keep in Dock so it is easy the next time to open it when you need it.

The script you want the VBA code to run looks like this now in the script editor.

Paste that inside the editor
Press the Run button
Observe the Result area at the bottom
You see the result (True or False)

When your script works correctly in the script editor you have proved that nothing is wrong with the script. Now we go to step 2 to make the script ready for using it with AppleScriptTask.

2) Add the script inside a handler and test it

Replace the script in the Script Editor with the script below.

And for testing only we copy this line at the top

ExistsFile("/Users/RDB/Desktop/MacTestFile.xlsm")

You can use this line instead if you want to use the colon separator

ExistsFile("Macintosh HD:Users:RDB:Desktop:MacTestFile.xlsm")

So it looks like this :

Now save the file as MyFileTest.scpt on your Desktop.
Use (File>Save)in the Script Editor Menu bar in the top left corner of your Mac window

You see that I placed the code inside a handler named ExistsFile which takes a parameter string of filepath. The name of the handler and parameter string is your choice. You see also in the script line that I replaced both path/file name strings with the variable filepath. This works the same as in the VBA code example for Excel 2011 where we have a string named FileName that we created in the macro.

Before we try to run the script with AppleScriptTask in VBA we first test the handler inside the Script Editor. The first line in the script is there only for testing.

Press the Run button, and the script test if the file exists on your Mac. The line above the ExistsFile handler provides the filename string to the ExistsFile handler, to enable you to test the script before we take the next step of calling it from VBA.

Before we go to the next step remove the script line above the handler or make it a comment, so the script does not use it. You do this by adding two hyphens before the line so it looks like this:

--ExistsFile("/Users/RDB/Desktop/MacTestFile.xlsm")

Click on the Run button and you see that nothing happens, because the handler has no filename string to test. But it test and indents the changes in the script. It is important before you close a file after you make changes to press the Run button.

Close the script file now and you will notice that it has automatically saved your changes.

3) Where to place the script file for using it with AppleScriptTask

Now the script file is ready and tested we must copy it into the correct location.
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 MyFileTest.scpt to the com.microsoft.Excel folder.

Note: 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.

This are three ways to easily open the com.microsoft.Excel folder manual :

Add it to your Favorites in Finder by dragging it to it while holding the Alt key down.
Add it to your Favorites in Finder with the shortcut : cmd Ctrl T
Drag the folder to the Desktop with the CMD and Alt key down. You now have a link to the folder on your desktop so it is easy to find it and open it in the future.

Note : Adding the folder to your Favorites is my favorite because you see the folder in your open and save dialogs in Excel.

Or use this script one time to create a folder for your Office files and create shortcuts to a few important folders,
check out this page : Setup your Mac for Mac Office

4) Use the script we create in VBA with AppleScriptTask

When you use AppleScriptTask the third argument is a parameter string that you use to give information to the handler. In the example on this page this must be the file path and name of the file that we want to test for.

This is the code line that you use in your VBA code. You see that there are three arguments (script file name, handler, and the filepath/name to check)

You can also add code to your workbook that check if the scpt file is in the correct location, copy the function below in the same module as your macro :

You can add this to your macro to stop it when the scpt file is not in the correct location

More Information

More than one handler in your script file

You can have more than one handler in the scriptfile; in the screenshot below I have also added a handler to test whether a nominated folder exists on your Mac.

Calling the folder test in VBA looks like this:

RunMyScript = AppleScriptTask("MyFileTest.scpt", "ExistsFolder", "/Users/RDB/Desktop/YourFolder/")

Use string wih more than one parameter in AppleScriptTask

AppleScriptTask can accept only one parameter string, but I found a workaround. See how I use SplitString in the applescript below to use more parameters.

More AppleScriptTask examples

Below you find some example scrips that you can copy in a scpt file and run it with VBA. I call the scpt file FileFolder.scpt in the macros below. So copy the scrips below in a file FileFolder.scpt and copy it in the correct location on your Mac. You see that the check file and folder scripts from above are also in the scrips below.

--Ron de Bruin, www.rondebruin.nl
--15-aug-2020
--Applescripts for working with files and folders
 
 
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
 
 
on OpenFolder(folderPath)
      if ExistsFolder(folderPath) = true then
             do shell script "open " & quote & folderPath & quote
      else
             return "Error"
      end if
end OpenFolder
 
 
on CopyFile(paramstring)
      set {fieldvalue1, fieldValue2} to SplitString(paramstring, ";")
      if ExistsFile(fieldvalue1) = true and ExistsFolder(fieldValue2) = true then
             set sourceFile to fieldvalue1
             set sourceFile to quoted form of POSIX path of sourceFile
             set destinationFolder to quoted form of POSIX path of (fieldValue2)
             do shell script "cp " & sourceFile & space & destinationFolder
      else
             return "Error"
      end if
end CopyFile
 
 
on CopyFolder(paramstring)
      set {fieldvalue1, fieldValue2, fieldValue3} to SplitString(paramstring, ";")
      if ExistsFolder(fieldvalue1) = true and ExistsFolder(fieldValue2) = true and ExistsFolder(fieldValue2 & fieldValue3) = false then
             set Sourcefolder to quoted form of POSIX path of fieldvalue1
             set destinationFolder to quoted form of POSIX path of (fieldValue2 & fieldValue3)
             do shell script "cp -r -n " & Sourcefolder & space & destinationFolder
      else
             return "Error"
      end if
end CopyFolder
 
 
on CopyFolderMerge(paramstring)
      set {fieldvalue1, fieldValue2, fieldValue3} to SplitString(paramstring, ";")
      if ExistsFolder(fieldvalue1) = true and ExistsFolder(fieldValue2) = true then
             set Sourcefolder to quoted form of POSIX path of fieldvalue1
             set destinationFolder to quoted form of POSIX path of (fieldValue2 & fieldValue3)
             do shell script "ditto " & Sourcefolder & space & destinationFolder
      else
             return "Error"
      end if
end CopyFolderMerge
 
 
--Note : the script below is used by some of the scrips above
on SplitString(TheBigString, fieldSeparator)
      tell AppleScript
             set oldTID to text item delimiters
             set text item delimiters to fieldSeparator
             set theItems to text items of TheBigString
             set text item delimiters to oldTID
      end tell
      return theItems
end SplitString

And below you find the VBA macros to call the scrips above

Sub OpenFolderInFinder()
'If you want to easy copy the path of the folder that you want to open in the script then
'Open the folder in Finder and be sure that in the Finder>View menu
'Show Path Bar is on, right click on the folder in the bar and choose copy ... as pathname
'Example below will open the Desktop in Finder, no need to change the code to test
Dim RunMyScript As String
Dim FolderPath As String

On Error Resume Next

FolderPath = MacScript("return POSIX path of (path to Desktop) as string")

'Also Desktop but you must enter the path yourself
'FolderPath = "/Users/rondebruin/Desktop"

'Example to open a folder named Ron that is on the Desktop
'FolderPath = MacScript("return POSIX path of (path to Desktop) as string") & "Ron"

RunMyScript = AppleScriptTask("FileFolder.scpt", "OpenFolder", FolderPath)
If RunMyScript = "Error" Then
    MsgBox "Wrong folder path"
End If
End Sub


Sub CopyFile()
'Copy file to another folder and replace if it already exists
Dim RunMyScript As String
Dim SourceFile As String
Dim DestinationFolder As String

'Add the path to the Source file and Destination folder
SourceFile = "/Users/rondebruin/Desktop/Source/Book1.xlsx"
DestinationFolder = "/Users/rondebruin/Desktop/Destination/"

'Add a slash at the end of the DestinationFolder path if the user forget it
    If Right(DestinationFolder, 1) <> Application.PathSeparator Then
        DestinationFolder = DestinationFolder & Application.PathSeparator
    End If
    
RunMyScript = AppleScriptTask("FileFolder.scpt", "Copyfile", SourceFile & ";" & DestinationFolder)

 If RunMyScript = "Error" Then
    MsgBox "Wrong file or folder path"
 Else
    MsgBox "Copy is ready"
 End If
End Sub


Sub CopyFolder()
'Copy folder to another folder
'If the destination folder not exits it will be created
'You can rename the Folder, see NameFolderInRootFolder
Dim RunMyScript As String
Dim SourceFolder As String
Dim DestinationRootFolder As String
Dim NameFolderInRootFolder As String

'Add the path to the Source and Root Destination folder
SourceFolder = "/Users/rondebruin/Desktop/Source/"
DestinationRootFolder = "/Users/rondebruin/Desktop/Destination/"

'Add the name of the folder in the root destination folder, this example add the Date/Time
NameFolderInRootFolder = "Source " & Format(Now, "dd-mmm-yy h-mm-ss")

'Add a slash at the end of the DestinationRootFolder path if the user forget it
    If Right(DestinationRootFolder, 1) <> Application.PathSeparator Then
        DestinationRootFolder = DestinationRootFolder & Application.PathSeparator
    End If

RunMyScript = AppleScriptTask("FileFolder.scpt", "Copyfolder", SourceFolder & ";" & DestinationRootFolder & ";" & NameFolderInRootFolder)

 If RunMyScript = "Error" Then
    MsgBox "Wrong path source or destination folder or destination folder already exists"
 Else
    MsgBox "Copy is ready"
 End If
  
End Sub


Sub CopyMergeFolder()
'Copy folder to another folder
'If the destination folder not exits it will be created
'if it exist it will merge the folder with changed and new files
'Note: It will not remove files
'You can rename the Folder, see NameFolderInRootFolder
Dim RunMyScript As String
Dim SourceFolder As String
Dim DestinationRootFolder As String
Dim NameFolderInRootFolder As String

'Add the path to the Source and Root Destination folder
SourceFolder = "/Users/rondebruin/Desktop/Source/"
DestinationRootFolder = "/Users/rondebruin/Desktop/Destination/"

'Add the name of the folder in the root destination folder
NameFolderInRootFolder = "Backup"

'Add a slash at the end of the DestinationRootFolder path if the user forget it
    If Right(DestinationRootFolder, 1) <> Application.PathSeparator Then
        DestinationRootFolder = DestinationRootFolder & Application.PathSeparator
    End If

RunMyScript = AppleScriptTask("FileFolder.scpt", "CopyfolderMerge", SourceFolder & ";" & DestinationRootFolder & ";" & NameFolderInRootFolder)

 If RunMyScript = "Error" Then
    MsgBox "Wrong path source or destination folder"
 Else
    MsgBox "Copy is ready"
 End If
  
End Sub