Calling MS Excel Macros and Functions from AppleScript

ID: Q132437

The information in this article applies to:

SUMMARY

Apple Computer, Inc., has defined an English-like scripting language system for the Macintosh called "AppleScript." This system is similar in power and flexibility to UNIX shell scripting, but it is much easier to use.

This article discusses the following topics:

SUMMARY

SAMPLE CODE FOR CALLING MACROS RETURNING VALUES WITH APPLESCRIPT HANDLING ERRORS MORE INFORMATION

OVERVIEW OF CALLING A MACRO WITH APPLESCRIPT

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft Support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web: 

   http://www.microsoft.com/support/supportnet/refguide/default.asp
AppleScript is manufactured by Apple Computer, Inc., a vendor independent of Microsoft; we make no warranty, implied or otherwise, regarding this product's performance or reliability.

You can use the following commands (provided by Microsoft Excel to AppleScript) in an AppleScript script, to call a macro contained in a Microsoft Excel document 

   do script <text expression>

   -or-

   Evaluate <text expression>
where <text expression> is a text string argument that Microsoft Excel will evaluate in order to perform the requested action. In both cases, <text expression> can be ALMOST any valid Microsoft Excel function or macro expression.

EXPLANATION OF "do script" AND "Evaluate"

Both of the above commands have similar syntax and functionality with respect to issuing stand-alone expressions. For example, the following two statements have identical results: 

   do script "SUM(2,3)"
   Evaluate  "SUM(2,3)"
In both cases, the value 5 is returned to the special AppleScript variable called "result."

The following two commands also have identical results: 

   do script "select(\"r1c1:r1c3\")"
   Evaluate  "select(\"r1c1:r1c3\")"
Each of these commands sets the selection on the active sheet to the cell range "A1:C1."

NOTE: All cell references sent to Microsoft Excel from AppleScript must be in R1C1 notation. Also note that quotation marks that are embedded within a text string must be preceded by a backslash.

The syntax and limitations for the above two commands varies with respect to calling a user-defined macro that exists within a Microsoft Excel document. The SAMPLE CODE FOR CALLING MACROS section of this article explains, in more detail, this difference in syntax.

SAMPLE CODE FOR CALLING MACROS

The following sections contain examples you can use to call Microsoft Excel macros from AppleScript.

SAMPLES FOR MICROSOFT EXCEL 4.0

The following examples assume that the document containing the macro is currently open in Microsoft Excel.

To run a macro, use a statement similar to the following 

   do script "macro1!test1()"
where the macro has the defined name "test1" on the open macro sheet named "macro1." The macro name, macro sheet name, exclamation point, and parentheses are required.

If the Macro Accepts an Argument

Place the argument inside the parentheses, as in the following examples. 

   Example 1
   ---------

   This command passes the value 5 to the macro:

      do script "macro1!test1(5)"

   Example 2
   ---------

   This command runs the same macro described in Example 1, but passes the
   value 7 to the macro. The value 7 is contained in the AppleScript
   variable x:

      copy 7 to x
      do script "macro1!test1(" & x & ")"

If the Macro Is Not Defined as a Command Macro

To run a macro beginning at cell A1 of the macro sheet named "macro1," use the following command: 

   do script "macro1!r1c1()"
You do not need to define the macro as a command macro.

To pass arguments using this type of command, use the same syntax as "Example 1" and "Example 2" in the "If the Macro Accepts an Argument" section of this article (above).

If the Macro Is in a Bound Sheet in a Workbook

To run a macro that is in a bound sheet in a workbook, use the following command 

   do script "[Workbook1]macro1!test1()"
where the "macro1" macro sheet is a bound sheet within the workbook "Workbook1."

NOTE: This syntax will result in an error if "macro1" is an unbound sheet. In this case, use the syntax described under the "Examples for Microsoft Excel 4.0" heading earlier in this article. To pass arguments with this type of command, use the same syntax as "Example 1" and "Example 2" in the "If the Macro Accepts an Argument" section of this article (above).

If the Macro Is Defined on the Active Macro Sheet

To run a macro named "test1" on the active macro sheet, use the following command: 

   Evaluate "run(\"test1\")"

   -or-

   Evaluate "macro1!test1"

If the Macro Is Defined On An Open Macro Sheet

To run the macro "test1" when the sheet "macro1" is already open, use the following command: 

   Evaluate "run(macro1!test1)"
If the Macro Sheet Containing the Macro Exists in the Current

Working Folder and Another File of the Same Name is not Already Open

To run the macro named "test1" which is defined on the macro sheet named "Macro1," use either of the following commands: 

   Evaluate "run(\"macro1!test1\")"
To run the macro beginning at cell A1 on the macro sheet named "macro1," use either of the following commands: 

   Evaluate "run(\"macro1!r1c1\")"

   -or-

   Evaluate "macro1!r1c1"
Note that if "macro1" is not open, Microsoft Excel will open it (provided it exists in the current working folder).

To pass the value 5 to the macro, use either of the following commands: 

   Evaluate "run(macro1!test1(5))"

   -or-

   Evaluate "macro1!test1(5)"
Note that the document "macro1" must already be open.

To run the macro beginning in cell A1 of the macro sheet "macro1" and pass the value five (5) to the macro 14, use the following command: 

   Evaluate "macro1!r1c1(5)"
Note that the macro sheet "macro1" must already be open.

SAMPLES FOR MICROSOFT EXCEL 5.0

Calling VB for Applications Subroutines and Functions

The syntax for calling Visual Basic, Applications Edition, subroutines and functions created in Microsoft Excel 5.0 is similar to the syntax described above for Microsoft Excel 4.0 macros, with the following exceptions:

1. Even if the module sheet containing the subroutine or function is the 

   active sheet, you must specify, at minimum, the module sheet name. For
   example:

      do script "Module1!MySub()"

   If "MySub" is a unique identifier within its parent workbook, you can
   use the workbook name and exclude the module name as in the following
   example:

      Evaluate "Workbook1!MySub()"
2. You must include parentheses, even if your subroutine or function does 
   not accept any arguments.

Calling Microsoft Excel 4.0-Style Macros

The syntax for calling a macro written in the Microsoft Excel 4.0 macro language from AppleScript is essentially the same as it is in Microsoft Excel 4.0 (for more information about this syntax, see the "Examples for Microsoft Excel 4.0" section above); however, if the macro is not within the active workbook, you must use the workbook name in the reference to the macro as in the following example: 

   Evaluate "run(\"Workbook1!test1\")"
The above example assumes that the name for macro "test1" is defined as a global name in "Workbook1." If this is not the case, then use syntax similar to the following: 

   Evaluate "run(\"[Workbook1]Macro2!test2\")"
The above will be especially necessary when two macros are defined with the same name and exist within the same workbook. In the above examples, "Workbook1" is the name of the workbook, and "Macro2" is a macro sheet within that workbook that contains the macro named "test2."

RETURNING VALUES WITH APPLESCRIPT

RETURNING VALUES USING THE MICROSOFT EXCEL 4.0 MACRO LANGUAGE

It is recommended, but not required, that a Microsoft Excel macro invoked from AppleScript return a value. If the macro ends with the RETURN() function, but it does not return a value, an error will be returned to AppleScript from Microsoft Excel. Error handling is described in a section below. It may be desirable for your macro to return a value that your AppleScript script can process.

The following examples include explanations of side effects that occur when code is invoked from an AppleScript script.

Returning the Boolean Value TRUE

The following macro will display an alert message, and return the Boolean value TRUE to AppleScript. This is also the value returned automatically by a Visual Basic subroutine in Microsoft Excel 5.0 (described in more detail below). 

   =ALERT("Run From AppleScript")
   =RETURN(TRUE)
If your macro does not need to return a specific value other than TRUE, it is recommended that TRUE be used as the return value to ensure consistency with Microsoft Excel 5.0 Visual Basic subroutines. The value returned can be any numeric, Boolean, or string expression.

The following macro will display an alert message and then cause an error to be returned to the calling AppleScript script from Microsoft Excel: 

   =ALERT("Run From AppleScript")
   =RETURN()
To handle this error condition, see the "Handling Errors" section later in this article.

Returning the Boolean Value FALSE

To display the alert message, terminate normally, and cause the Boolean value FALSE to be returned to AppleScript, use the following code: 

   =ALERT("Run From AppleScript")
   =HALT()

Syntax That Returns an Error Message in Microsoft Excel

The macro below will, after displaying the alert message, cause an error message to be displayed in Microsoft Excel (requiring the user to interact with the message). This message will state that no RETURN() or HALT() was encountered. After the error message is dismissed, the Boolean value false is returned to AppleScript (unless a time-out has occurred). 

   =ALERT("Run From AppleScript")
The macro below will, after displaying the alert message, terminate normally and return the Boolean value false to AppleScript. The command ERROR(FALSE) disables the error message that Microsoft Excel would normally display because it did not encounter a RETURN() or HALT() command in the macro. 

   =ERROR(FALSE)
   =ALERT("Run From AppleScript")

RETURNING VALUES USING VISUAL BASIC FOR APPLICATIONS

Macros

A Visual Basic subroutine in Microsoft Excel 5.0 is only capable of returning two values (the Boolean values TRUE and FALSE) to AppleScript. The Boolean value TRUE is returned when the subroutine completes successfully. The Boolean value FALSE is returned when the subroutine terminates abnormally, due to a run-time error or a user interrupt.

Functions

A Visual Basic function can return any numeric, Boolean, or string expression to AppleScript. If no value is explicitly returned by the function, the real value zero (0.0) is returned. If the function terminates abnormally, due to a run-time error, the Boolean value FALSE is returned to AppleScript. All numeric data returned to AppleScript will be of the AppleScript data type "real."

HANDLING ERRORS

The information in the above sections provides examples of macros that do and do not generate errors in AppleScript. Should you find yourself in a situation where you must use one of the forms that does generate an error, AppleScript has some mechanisms in place for handling this event, without causing your AppleScript script to terminate abnormally. The information presented in this section describes commands that are specific to AppleScript version 1.1; therefore, these commands may be different for later versions of this product.

Microsoft Corporation has no control over the syntax or functionality of the AppleScript commands described in this section. Microsoft Corporation also makes no claims as to the validity of the commands described in this section if you are using a scripting system other than AppleScript.

Ignoring Application Responses

Consult your AppleScript documentation for more information about the ignoring (or considering) qualifier.

Example:

tell application "Microsoft Excel" 

   ignoring Application Responses
      do script "workbook1!mySub()"
   end ignoring
end tell

The above example tells Microsoft Excel to execute a macro named "mySub" contained in the document "workbook1." However, because AppleScript has been instructed to ignore Application Responses, AppleScript will not wait for Microsoft Excel to complete execution of the macro and return a value. Any value returned by the macro will be ignored. Using this method, it is possible for AppleScript to continue processing the rest of the script, following the call to the macro, and may in fact complete execution of the script before Microsoft Excel finishes executing the macro.

Using the "try ... on error" Error Handler

Consult your AppleScript documentation for more information about the "try" statement error handler.

Example:

tell application "Microsoft Excel" 

   try
      do script "Macro1!myMacro()"
   on error myErrorText number myErrorNumber
      display dialog myErrorText & return & (myErrorNumber as string)
   end try
end tell

The above example tells Microsoft Excel to execute a macro named "myMacro" contained on the macro sheet "Macro1." This time, if an error occurs as the result of executing the macro, the lines between "on error" and "end try" are executed. Below is a description of what these lines do.

This line 

   on error myErrorText number myErrorNumber
places a descriptive error message as a text string into the user- defined AppleScript variable "myErrorText". It also places an error number into the user-defined AppleScript variable "myErrorNumber".

This line 

   display dialog MyErrorText & return & (myErrorNumber as string)
because it is contained within the "on error" clause of the "try" statement, is only executed when an error results. In this example, it displays the descriptive error message retrieved in step 1 on the first line of a dialog box. It also displays the error number retrieved in step 1 on the last line of the same dialog box.

NOTES:

Sample Code for Error Handling

The following example demonstrates how you can use error handling to ensure smooth operation of a script that calls a Microsoft Excel 5.0 Visual Basic subroutine. In this example, a macro named "RunFromAS" is saved within a workbook file named "MacroBook." MacroBook is saved on the Desktop. The script checks if MacroBook is open before calling the macro. If it is not open, an attempt is made to open it. If it can not be opened, an error message is displayed, but the script does not terminate abnormally. If MacroBook is opened successfully or if it is already open, then an attempt is made to run the macro "RunFromAS." If an error is encountered at this point, a message is displayed, but the script does not terminate abnormally. 

   tell application "Microsoft Excel"
      Activate
      set runIt to true
      if not (exists Workbook "MacroBook") then
         try
            Open "Macintosh HD:Desktop Folder:MacroBook"
         on error myErrorText
            set runIt to false
            display dialog myErrorText buttons "OK"
   default button 1
         end try
      end if

      if runIt then
         try
            do script "MacroBook!RunFromAS()"
         on error myErrorText
            display dialog myErrorText buttons "OK"
   default button 1
         end try
      end if
   end tell

MORE INFORMATION

Explanation of the AppleScript Dictionary

Microsoft Excel contains an "aete" resource and the necessary Apple Event capability to fulfill the scriptable level of AppleScript support. To use the application-specific capabilities of Microsoft Excel with AppleScript, open and examine the AppleScript dictionary supplied with Microsoft Excel.

To open the dictionary in Microsoft Excel using the Script Editor, do the following:

1. Start the Script Editor.

2. From the File Menu, choose Open Dictionary.

3. In the dialog box presented, switch to the folder containing Microsoft 

   Excel.
4. Select Microsoft Excel and click the Open button.

The window presented will allow you to select an object or a class to view its description. You can also click the bold suite names in order to view an entire suite at one time. The descriptions shown in this window can be used to create scripts within the Script Editor to control Microsoft Excel. Note that certain suites may contain too much information to be displayed at one time. In this case, select only a portion of the topics listed under this suite's bold entry in the list. One example of such a suite is the Core Suite from Microsoft Excel version 5.0.

Where to Get More Help About Using AppleScript with Microsoft Excel

More information on this topic may be available from Microsoft Information Services. Microsoft Information Services are available on the World Wide Web by connecting to http://www.microsoft.com. The FTP site is located at ftp.microsoft.com.

REFERENCES

"AppleScript Language Guide," version 1.0, Chapter 7-18, "Considering and Ignoring Statements"

"AppleScript Language Guide," version 1.0, Chapter 9, "Error Handlers"

For more information about AppleScript, choose the Search button in Help and type: 

    AppleScript
Additional query words: 4.00 5.00 Apple Script execute 
Keywords          : kbprg PgmHowto 
Version           : MACINTOSH:4.0,5.0
Platform          : MACINTOSH
Issue type        : kbhowto

Last Reviewed: May 19, 1999