AppleScriptTask in Office 2016 for the Mac
VBA for Mac Word vs. Word for Windows. Article contributed by George Clark. A Developer’s Perspective. Generally speaking the differences between Word for Mac and Word for Windows are minimalat least from the user’s point of view. How to open the VBA environment. You can access the VBA environment in Excel 2016 by opening the Microsoft Visual Basic for Applications window. First, be sure that the Developer tab is visible in the toolbar in Excel. The Developer tab is the toolbar that has the buttons to open the VBA editor and create Form/ActiveX Controls like buttons, checkboxes, etc. The VBA Editor is where you type, edit and debug your VBA code. A VBA project organizes your code in a workbook. Microsoft Excel Visual Basic for Applications (VBA) is one the most advanced versatile programming environments on the market today for developing advanced business, engineering and scientific tools.
Use VBA to automate Power Query in Excel 2016 Are you familiar with Power Query for Excel 2010 & 2013? In Excel 2016 you can now use Macro Recording and Object Model (VBA, PowerShell, C# etc.) to automate your ETL solutions. Development on VBA ceased in 2004, and a new version called VBA-M was released in 2009. VBA-M is designed for Windows, and all you'll need is the VBA-M executable and your ROM files. If you're using Mac OS X or Linux, you'll need to install RetroArch, a multi-emulator that includes the VBA-M core.
In Office 2011 for the Mac there are many problems with the built-in VBA commands. For Example Dir, Kill, GetOpenFilename, GetSaveAsFileName and many others. For example, in Office 2011 file names in VBA are limited to a maximum of 32 characters including the extension. If the file name is longer the code fails.
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, Excel 2011 and Outlook 2011.
In Office 2016, we need to use a new method and a new approach explained below.
Example for 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 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
Macintosh HD:Users:RDB:Desktop:MacTestFile.xlsm
exists on your Mac and display a msgbox showing True or False
Excel 2016 for the Mac
If you test the code that is working correctly in Excel 2011, in Excel 2016 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.
What’s the problem here?
The legacy 'MacScript' VB Command is severely limited by Apple’s sandbox requirements: it will not work correctly in most situations in Office 2016. Updating the MacScript function seems to be too difficult.
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:
tell application 'System Events' to return (exists disk item 'Macintosh HD:Users:RDB:Desktop:MacTestFile.xlsm') and class of disk item 'Macintosh HD:Users:RDB:Desktop:MacTestFile.xlsm' = file
- 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
How To Find The Vba Editor For Macro
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
- The Script Editor must be active to see the Script Editor menu bar.
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, which 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')
First released almost thirty years ago, this powerful editing is preferred by some of the world's top designers and can be utilized to create almost anything the imagination can conjure up. Best photoshop software for mac free.
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 have add one or both folders and have problems with the code on this page reboot your Mac first and test it again.
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. Unfortunately there is no folder for all Office programs.
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.
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:
- The script file name
- The name of the handler
- The file name you want to check
RunMyScript = AppleScriptTask('MyFileTest.scpt', 'ExistsFile', '/Users/RDB/Desktop/MacTestFile.xlsm')
So your VBA macro now looks like this :
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 more than one parameter string with AppleScriptTask
AppleScriptTask can accept only one parameter string, but I found a workaround. See my Mail example codes for 2016 if you want to know how to do this : http://www.rondebruin.nl/mac/mail.htm
Free youtube tumbnail editor for mac. Don’t just use any old image — you’ll need to ensure several things: • The resolution should be 1280 x 720 pixels. Dark images don’t look the best in the YouTube sidebar. • Use an image that stands out.
Create SCPT files with VBA code and copy it in the com.microsoft.Excel folder
It is possible to create or update scpt files only with VBA code, but before you can do this you must do some things manual first because Apple not allow you to create the path with VBA code. See point 3 above how to create the path below.
Library/Application Scripts/com.microsoft.Excel/
Then copy the MakeSCPTFile.scpt file from the download above inside the com.microsoft.Excel folder.
You must do this only one time and after that you can do everything with VBA code.
Copy the excel workbook on your desktop and test the code and see if it create a new scpt file inside the com.microsoft.Excel folder for you, you see that you create the script also in the VBA macro. If you change the string in the macro and run the code again it will overwrite the existing scpt file, this way you can update the scpt file.
After reading all the great things about Microsoft Excel for Mac 2011 that were written before its release, I decided to purchase a copy. Open dwg on mac. My primary goal was to have an Excel version on my MacBook Pro that allowed VBA macros, however the full Office software suite seemed to be a really great value.
I paid $174.99 USD for the Microsoft Office for Mac Home and Business 2011 – 1 Pack edition on amazon.com after pre-ordering and receiving a $25 discount from their price assurance guarantee.
Excel 2011 Software Installation
What you get with the Office 2011 Home and Business Edition:
Free Vba Editor
- Word 2011
- PowerPoint 2011
- Excel 2011
- Outlook 2011
- Microsoft Office Web App support
- Messenger for Mac 8
- Remote Desktop for Mac 2 (drive your Windows-based PC from your Mac)
- Technical support 1 year
The installation took about 15 minutes. The program loaded, I entered the product key, activated and registered the software, then launched Excel, and got a welcome screen telling me all about the new features.
And then I had to face the Excel Workbook Gallery, which is the default screen that appears when you open Excel 2011. Nice for all of 15 seconds.
At the bottom of this screen there’s a box you can check: Don’t show this when opening Excel, but I ignored it and went straightaway to a blank Excel workbook.
Excel 2011 Preferences
The first thing I wanted to check out was the Options, oops pardon my Windows speak, I mean Preferences, by using the menu selection Excel → Preferences or the keyboard shortcut Cmd+apostrophe ( ⌘ , ).
Within these preferences is standard stuff you’d find in a Windows version of Excel, but I want to review some of the settings for General, Edit, AutoComplete, Compatability, and Ribbon.
General Preference
In the General dialog box I unchecked the box beside: Open Excel Workbook Gallery when application opens so that Excel opens to a blank worksheet.
![Where is the vba editor in excel Where is the vba editor in excel](https://www.techonthenet.com/excel/macros/images/visual_basic_editor2013_004.png)
Edit Preference
On the Edit dialog box, a new option for Excel 2011 is Automatically convert date system. As you can see in the Description box below, this option converts the date system of the source data to match the date system of the target workbook.
I’m not exactly sure how this preference option works and will be looking into it, but with Excel’s two different date systems (1900 & 1904) this setting becomes more important now that Excel 2011 and Excel 2010 can share workbooks on the web in Windows SkyDrive.
AutoComplete Preference
In the AutoComplete dialog box the setting for Show the menu only after I’ve typed x letters (x set at 1, range 0-9) seems to be one that I’ll revisit soon. I’m not sure I want AutoComplete popping up after typing only 1 letter.
CompatibilityPreference
On the Compatibility dialog box, under Transition, the Save files in this format: is defaulted to Excel Workbook (.xlsx) and can be set to a number of different things.
Changing the default file format to Excel 97-2004 Workbook (.xls) might be prudent should you share files frequently with people using older versions of Excel.
Ribbon Preference
On the Ribbon dialog box, under Show or hide tabs, I noticed that you can drag them in the order you prefer. I added the Developer tab to the Excel Ribbon by checking the box for Developer.
The VBA Editor – A Quick Look
I had to peek at the VBA editor just to check it out. Going to the Developer tab and clicking Editor opened the VBA Editor, where I added a module and wrote a quick test macro, as you can see below.
Not a bad start.
The Vba Editor
Next I’ll compare the Ribbons of Excel 2011 and 2010.
Related posts: