Calling MS Excel Macros and Functions from AppleScript

• Microsoft Excel for Macintosh, versions 4.0, 5.0

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

• OVERVIEW OF CALLING A MACRO WITH APPLESCRIPT
• EXPLANATION OF "do script" AND "Evaluate"

• SAMPLES FOR MICROSOFT EXCEL 4.0

      - If the Macro Accepts an Argument
      - If the Macro Is Not Defined as a Command Macro
      - If the Macro Is in a Bound Sheet in a Workbook
      - If the Macro Is Defined on the Active Macro Sheet
      - If the Macro Sheet Containing the Macro Exists in the Current
        Working Folder and Another File of the Same Name Is Not Already Open
  

• SAMPLES FOR MICROSOFT EXCEL 5.0

      - Calling VB for Applications Subroutines and Functions
      - Calling Microsoft Excel 4.0 Style Macros
  

• RETURNING VALUES USING THE MICROSOFT EXCEL 4.0 MACRO LANGUAGE

      - Returning the Boolean Value TRUE
      - Returning the Boolean Value FALSE
      - Syntax that Returns an Error message in Microsoft Excel
  

• RETURNING VALUES USING VISUAL BASIC SUBROUTINES

      - Macros
      - Functions
  

• Ignoring Application Responses
• Using the "try ... on error" Error Handler
• Sample Code for Error Handling

• Explanation of the AppleScript Dictionary
• Where to Get More Help About Using AppleScript with Microsoft Excel

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 engineers 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

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>

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)"

The following two commands also have identical results:

   do script "select(\"r1c1:r1c3\")"
   Evaluate  "select(\"r1c1:r1c3\")"

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()"

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()"

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()"

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)"

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\")"

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

   -or-

   Evaluate "macro1!r1c1"

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

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

   -or-

   Evaluate "macro1!test1(5)"

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)"

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\")"

   Evaluate "run(\"[Workbook1]Macro2!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)

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()

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")

   =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

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

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

This line

   display dialog MyErrorText & return & (myErrorNumber as string)

NOTES:

• The error number returned from Microsoft Excel when a macro terminates without returning an explicit value is -1708.
• The "display dialog" command is an AppleScript command whose functionality is provided by a Scripting Addition. A Scripting Addition is an add-on to the AppleScript scripting environment. The scripting addition "display dialog" is provided by Apple Computer, Inc. It should be located within the Scripting Additions folder, which is located inside the Extensions folder inside your System Folder.

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.

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