Microsoft KB Archive/132437

{|
 * width="100%"|

Calling MS Excel Macros and Functions from AppleScript

 * }

-

The information in this article applies to:


 * Microsoft Excel for the 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"

SAMPLE CODE FOR CALLING MACROS


 * 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 WITH APPLESCRIPT


 * 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
 * HANDLING ERRORS


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

MORE INFORMATION

 * 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 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/overview/overview.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

-or-

Evaluate where is a text string argument that Microsoft Excel will evaluate in order to perform the requested action. In both cases, 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.

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:

<ol> <li> 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: <pre class="CODESAMP">     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: <pre class="CODESAMP">     Evaluate "Workbook1!MySub" </li> <li>You must include parentheses, even if your subroutine or function does not accept any arguments.</li></ol>

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:

<pre class="CODESAMP">  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:

<pre class="CODESAMP">  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 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).

<pre class="CODESAMP">  =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:

<pre class="CODESAMP">  =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:

<pre class="CODESAMP">  =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).

<pre class="CODESAMP">  =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.

<pre class="CODESAMP">  =ERROR(FALSE) =ALERT("Run From AppleScript")

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:

<pre class="CODESAMP">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:

<pre class="CODESAMP">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

<pre class="CODESAMP">  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

<pre class="CODESAMP">  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:


 * 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.

<pre class="CODESAMP">  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

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.