Excel and AppleScript

I found this article somewhere on the internet: it was a Word document. The author is unknown. I made some corrections and posted it here.

These lines are based upon different Microsoft documentuments, among other stuff. These documentuments provide some indications about the use of Excel and AppleScript and how to have them working together.

Important : with Excel:2001, it is possible to memorize actions through the AppleScript Editor!!! And remember to try Satimage’s “SMILE”, a better AppleScript Editor.

This documentument was originally written in French and intended to help French speaking users, using a French version of Excel. A good part of that document would be of no interest to English Excel users, so I made it somewhat shorter. I’m now attempting a translation back to English, so I hope there aren’t too many mistakes, and it’s not too far from the original texts I started from… and it still is worth a read.

1 — How to set Excel to work from an AppleScript

You can easily handle Excel from AppleScript... with some caretaking. You’ll have to put quotes around cells or range names (with "A1" style as well as "R1C1"). I know it’s basic, but it’s always worth a tell...

Here’s a first usable script:

tell application "Microsoft Excel"
    activate
    select sheet "Sheet1"
    select range "A1"
    set value of active cell to 1
    select range "A2"
    set formula of active cell to 2
    select range "A1:A2"
    autofill selection destination range "A1:A10"
end tell

It could be simpler, but it’s just to show how different ways to obtain the same result.

2 — Setting AppleScript to work through an Excel VBA macro

And now, how to use AppleScript from Excel’s VBA. A simple example:

Sub RunScript()
    Dim str As String, temp As String, CR As String
    CR = Chr$(13)
    str = "tell application ""Finder"""
    str = str & CR & "set DiskName to name of startup disk"
    str = str & CR & "end tell"
    temp = MacScript(str)
    MsgBox "Startup disk = " & temp
End Sub

The principle is to send to the Finder a string that’ll be executed as a Script. The instruction "MacScript" allows this. To obtain the different lines in the script, "carriage returns" ("Chr$(13)") are inserted at the right places in the string.
NOTE: To send a quote to AppleScript, you have to type two quotes in the VBA macro.

The instruction (here "str") has to be put between parentheses in order to obtain an answer from the script, be it a previously recorded script or just some instruction as above. The trick about the parentheses is that the result can be assigned to a variable (here "temp"), even if it’s not intended to be used further. It just keeps Excel waiting for the result before running the procedure any further. If no parentheses are placed, VBA just launches the script and moves on to the next instruction, not waiting till the script instructions have been executed (or not…)

Now, an Excel macro prompting to choose a text file and modify it’s "file type" and "creator type" to set them to those of an Excel created text document (but this is just an example, of course, it could be any other). The visible consequence of this is that the document will have an icon similar to those of the text files created by Excel... This is how to mix variables and AppleScript instructions.
NOTE: how the quotes around the "ThePath" variable are sent to the Finder.

Sub ChangeTypes()
    Dim DocumentChoice As Variant
    Dim str As String, temp As String
    Dim ThePath As String, CR As String

    DocumentChoice = Application.GetOpenFilename(FileFilter:="TEXT", _
        ButtonText:="Choose")
    If DocumentChoice = False Then
        MsgBox "Impossible choice, operation canceled."
        Exit sub
    Else
        ThePath = CStr(DocumentChoice)
        CR = Chr$(13)
        str = "tell application ""Finder"""
        str = str & CR & "set file type of file """ & ThePath & """ to ""TEXT"""
        str = str & CR & "set creator type of file """ & ThePath & """ to ""XCEL"""
        str = str & CR & "end tell"
        temp = MacScript(str)
    End If
End Sub

3 — AppleScript and Excel’s VBA working together

To launch an Excel macro from AppleScript and exchange data, here’s a somewhat more complex example. The result given by the VBA macro is sent back into Excel through the AppleScript. I know it’s not very interesting a deed, but it’s just to show how to get a result, and have the script waiting before executing the next instruction:

In Excel VBA, paste:

Function Multiplic(MyValue As Integer)
    [A1] = MyValue 
    MyValue = MyValue * 2
    Multiplic = MyValue 
End Function

In AppleScript editor, run :

tell application "Microsoft Excel"
    set intAValue to 4
    set MacroMulti to "MyWorkbook!Multiplic(" & intAValue & ")"
    set intMulti to evaluate (MacroMulti)
    set Value of Cell "A2" to intMulti
end tell

Note: It needs a "Function Xx()" to obtain a result. Using a "Sub Xx()" procedure, it’ll be launched, but gives only "True" or "False" as a result, depending on the execution of the procedure. The name of the workbook must be included in the macro name, even if it’s the active workbook.

According to Excel’s AppleScript dictionary, many things can be done in Excel through AppleScript. Mainly operations dealing with worksheets and graphics. As to the macros, it’s obviously preferable to "satisfy" with launching macros previously recorded in a VBA module, which is not that bad!!!

To be more efficient, it’s better to jump from one application to the other only when needed.

Hereunder, a part of the Microsoft document VBA & AppleScript. It concerns only the launching of VBA macros from AppleScript, but shows some use of the error codes I find interesting.

Crating a Macro

Start Microsoft Excel.
Start the Visual Basic editor (OPTION+F11), and click Module in the Insert menu.

In the module, type:

Sub Test_Recording()
    MsgBox "This is an Excel macro."
End Sub

In the File menu, click Close and return to Microsoft Excel.
In the File menu, click Save.
Save the workbook as "Workbook1".
Click Hide Microsoft Excel in the Application menu.

Recording the Script

To record the script:

Start the Script Editor.
In the Controls menu, click Memorize.
In the Application menu, click Microsoft Excel.
Select Macro in the Tools menu and click Macros...
In the list of macros, click Test_Recording, then Run.
A dialog now displays the message: "This is an Excel macro."
Click OK.
In the Application menu, click Script Editor.
In the Controls menu, click Stop.
The script reads:
```
tell application "Microsoft Excel"
	activate
	evaluate "Workbook1!Test_Recording()"
end tell
```
NOTE: To start Visual Basic procedures (including macros) from an AppleScript, the procedure name has to be followed by parentheses.
In the File menu, click Save.
Choose a name and a location for your script in the dialog box and click Save.

Runing the saved Script

To run the script:

The above saved script opened in the Script Editor, click Execute in the Controls menu.
The Test_Recording runs, the message "This is an Excel macro." pops up.
Click OK.
In the Application menu, click Script Editor.

Passing arguments to and from a Visual Basic procedure

The following examples pass arguments from an AppleScript to a VBA Sub or Function procedure.

As indicated, the procedure’s name must include the workbook’s name and be followed parentheses to function from an AppleScript. To pass arguments to a procedure, place them between the parentheses.

Passing a single value to a Sub procedure

To pass a value:

Open the previously created “Workbook1” workbook (Sheet1) then start the Visual Basic Editor (OPTION+F11).

Type:

Sub Pas_1_Argument(x As Variant)
    Worksheets("Sheet").Cells(1,1).Value = x
End Sub

In the File menu, click Close and return to Microsoft Excel.
In the File menu, click Save.
In the Applications menu, click Hide Microsoft Excel.
Start the Script Editor and type (in a new window):
```
tell application "Microsoft Excel"
    set MyValue to 10
    activate
    evaluate "Workbook1!Pass_1_Argument(" & MyValue & ")"
end tell
```
NOTE: To pass a variable to a Visual Basic procedure, the variable has to be concatenated inside the evaluate instruction. To pass a constant to the procedure, placing the value between parentheses is sufficient. In this case, the line becomes:
```
evaluate "Workbook1!Pass_1_Argument(10)" 
```
In the File menu, click Save.
Choose a name and a location for your script in the dialog box and click Save.
In the Controls menu, click Execute.
The script activates Microsoft Excel and places the value 10 in cell A1 of Sheet1.

Returning a value from a VBA Sub procedure

A Visual Basic Sub procedure returns the value 0 when it executes normally from a script.

If you call a Function procedure, you can return any numerical, boolean or string value to the AppleScript. Whether you are calling a Sub or a Function procedure, the value is returned to a predefined AppleScript variable named "result".

Returning a Value from a Function procedure

To return a value:

In Microsoft Excel open the previously created “Workbook1” workbook.

Start the Visual Basic Editor (OPTION+F11) and type in the module:

Function Return_Value(x As Integer) As Integer
    Return_Value = x * 2
End Function

In the Applications menu, click Hide Microsoft Excel.

Start the Script Editor and type (in a new window):

tell application "Microsoft Excel"
    evaluate "Workbook1!Return_Value(10)"
    set MyValue to result + 1
    display dialog MyValue
end tell

In the File menu, click Save.
Choose a name and a location for your script in the dialog box and click Save.
In the Controls menu, click Run.
An AppleScript dialog box displays the value 21.
Click OK.

Returning a value from a Sub procedure

To return a value from a Sub procedure:

In Microsoft Excel open the previously created “Workbook1” workbook.

Start the Visual Basic Editor (OPTION+F11) and type in the module:

Sub Return_Sub_Value()
    Sheets("Sheet1").Cells(1, 1).Value = 1
End Sub

In the Application menu, click Hide Microsoft Excel.

Start the Script editor and type (in a new window):

tell application "Microsoft Excel"
    evaluate "Workbook1!Return_Sub_Value()"
    display dialog result as integer
end tell

In the File menu, click Save.
Choose a name and a location for your script in the dialog box and click Save.
In the Controls menu, click Run.
An AppleScript dialog box displays the value 0.
Click OK.

Error Trapping Values Returned from a Procedure

There are situations in which your Sub or Function procedure fails because of user intervention or a run-time error. This section discusses ways to communicate to the AppleScript script that an error has occurred in the Visual Basic procedure.

Error Trapping for a Sub Procedure

A Visual Basic Sub procedure cannot explicitly pass a value back to its AppleScript caller. However, a Sub procedure can store a value in a worksheet cell, or it can store a defined name in the workbook. Then, your AppleScript script can check the current value of the cell or the defined name to see if an error has occurred. The following example uses a defined name to hold the status of any error in the Sub procedure:

Start Microsoft Excel and open the workbook “Workbook1” created earlier.
In the Insert menu, point to Name, and then click Define.
Type MyErr in the Names in workbook box.
Delete any text in the Refers To box, and then type ="ok" in the Refers To box. Click OK.

Start Visual Basic Editor and type the following code in the module:

Sub Return_Sub_Error()
    Dim x as Integer
    ThisWorkbook.Names("MyErr").RefersTo = "ok"

On Error GoTo handle:

    x = MsgBox(Prompt:="Click Yes for an error, otherwise" &  _
        "Click no", Buttons:=vbYesNo)
    If x = vbYes Then
        Error 1004 'Generates a run-time error.
    End If

Exit Sub

handle:
    ThisWorkbook.Names("MyErr").RefersTo = Err

End Sub

On the Application menu, click Hide Microsoft Excel.

Start Script Editor and type (in a new script window):

tell application "Microsoft Excel"
    activate
    evaluate "Workbook1!Return_Sub_Error()"
    if not(evaluate "Workbook1!MyErr") = "ok" then
        set MyErr to evaluate ("Workbook1!MyErr")
        set MyErr to MyErr as integer
        display dialog "An error occurred in the macro: " & MyErr
    else
        display dialog "No error."
    end if
end tell

On the File menu, click Save.
Select a destination folder, type a name for your script in the Save Script As box, and then click Save.
On the Controls menu, click Run.
The script activates Microsoft Excel and displays a message box.
Click Yes.
This creates an error condition in the Sub procedure and changes the value of the defined name "MyErr." Then, an AppleScript dialog box displays the following message:
"An error occurred in the macro: 1004."
Click OK.

NOTE: If you click No in step 10, the script displays a dialog box with the message "No error."

Error Trapping from a Function

Because a function can return a value to its caller, passing this information back to a script is much easier for a function than it is for a Sub procedure.

To trap errors for a function:

Switch to Microsoft Excel and open the workbook “Workbook1” created earlier.

Start Visual Basic Editor and type:

Function Return_Function_Error () As Integer
Dim x As Integer

On Error GoTo handle:

	x = MsgBox(Prompt:="Click Yes for an error, otherwise" & _
					"click No", Buttons:=vbYesNo)
    If x = vbYes Then
        Error 1004 ' Generates a run-time error.
	Else
		Return_Function_Error = 0
	End If
Exit Function

handle:
	Return_Function_Error = Err

End Function

On the Application menu, click Hide Microsoft Excel.

Start Script Editor and type (in a new script window):

tell application "Microsoft Excel"
    activate
    evaluate "Workbook1!Return_Function_Error()"
    if not (result = 0) then
        set MyErr to result as integer
        display dialog "An error occurred in the macro: " &  MyErr
    else
        display dialog "No error"
    end if
end tell

On the File menu, click Save.
Select a destination folder, type a name for your script in the Save Script As box, and then click Save.
On the Controls menu, click Run.
The script activates Microsoft Excel and displays a message box.
Click Yes.
This creates an error condition in the function, and the function value is set to the value of the error. Then, an AppleScript dialog box displays the following message:
"An error occurred in the macro: 1004."
Click OK.

NOTE: If you click No in step 7, the script displays a dialog box with the message "No error."