This tutorial has been created with Excel 2002 and J501. Expect different behavior with other versions!
It will help to have the Excel help file readily available.
Introduction
The purpose of OLE Automation is to allow a client program to run functions in a server program, and the basic idea is pretty straightforward - simply load J from Excel, then send it the required J functions for execution. In practice is it helpful to create Excel macros that provide cover functions for the basic tasks such as loading J, reading cells for transmission to J and so on. Thus you typically program with a mixture of J functions and Excel macros.
Functions provided by an OLE Server are referred to as methods, see J OLE Automation Server
In Excel, you can enter these method names in upper or lowercase. When you enter names in Excel, it gives them its default capitalization. Here we use lowercase throughout.
Troubleshooting
You are going to be working with both J and Excel sessions active. It will be helpful to close down other applications to minimize screen clutter.
As you use OLE, commands sent from Excel may change the active focus to J. To enter new commands in Excel, click on the Excel session to change the active focus back to Excel.
Most of the time when things go wrong, you can simply shut down J and Excel and start again. Sometimes, the J server has been loaded but is not visible. You can check this by pressing Ctrl+Alt+Del simultaneously, which brings up the list of current applications loaded. If necessary, select J and click End Task.
Sometimes when you edit Excel macros, Excel closes down J - it closes the OLE Automation object which may in turn cause J to close. You will then need to re-open the J OLE Automation object. If J has closed and you try to run an OLE command, the error message is "Object variable not Set". This problem occurs only while you are developing Excel macros, and should not occur when your application is in use.
One of the "user-friendly" features of Excel is to change your entry in a cell if it thinks it may be incorrect. For example, "i.5" gets changed into "I.5". To get around this, enter more letters, then backspace and delete the extra entries, for example, instead of "i.5' try entering "ii.5".
Tutorial
Start by unloading all applications, then loading Excel. Arrange the window so that it covers only about half the screen. Open a new workbook if none is shown.
Bring up Visual Basic (Alt-F11 or Tools|Macro|Visual Basic Editor), and insert a new module sheet (Insert|Module).
With the module sheet visible, select menu item Tools|References and check both J DLL Server and J EXE Server, and click OK. In practice you need only check the server that will be used.
In the module sheet, enter:
Public js As Object
Sub jopen()
Set js = CreateObject("jexeserver")
js.Quit
js.Do "BINPATH_z_=:1!:46''"
js.Do "ARGV_z_=:,<'oleclient'"
js.Do "(3 : '0!:0 y')<BINPATH,'\profile.ijs'"
End Sub
The function jopen will be used to load the JEXEServer. Note that you can only run this once - you will get an error at this point if you try to open the server twice.
The first statement declares the name js that will be used for the JEXEServer.
js.Quit ensures that when Excel is closed, the J server will automatically terminate.
The next three lines load profile and create and show an ijx window.
Loading J
Next open up the Immediate window for experimentation (if not already open). To do so, select menu item View|Immediate Window. You can enter a series of commands in this Window - when you press Enter, Excel runs the command in the line where the cursor is.
To load J, enter:
jopen
Experiment with show:
js.show 0 |
this hides the window |
js.show 1 | this shows it again |
This means: run the show method of js, i.e. of the JEXEServer, with the given argument.
The J OLE Automation Server should be visible. Arrange the windows so that both Excel and J are visible. Note that not only is the J Server visible, but if you click on it to give it focus then you have full access to the regular J development system.
Next set on logging - this tells J to display commands sent by Excel in the J window:
js.log 1
Sending commands to J
The required function is do, which takes a J sentence as its argument. Note that Excel strings are delimited by the double quote, so that J quotes can be entered as is, and need not be doubled. Try:
js.do "i.4 5"
You should see the statements and results in the J window.
Retrieving values from J
The function get retrieves a value from J, as a Variant datatype. Variants cannot be displayed directly in the Immediate window, but can be assigned to a worksheet range. For example:
Set value of x in J:
js.do "x=: i.4 5"
Retrieve value of x into Excel variant y:
js.get "x",y
Set value of y into the worksheet:
Worksheets("sheet1").Range("a1:e4")=y
Now switch to Sheet1 to see that the value of y has been written in.
Utilities
Now lets take a look at the J OLE utilities in file system\examples\ole\excel\jsutil.txt. Copy and paste the contents of this file into your Excel module.
The utilities available are:
jdopen | open JDLLServer |
jxopen | open JEXEServer |
jcmd (string) | execute J command, return result as variant |
jcmdc string,r,c,h,w | execute J command, store result in active sheet at row,col,height,width |
jcmdr string,range | execute J command, store result in active sheet at range |
jdo string | execute J command |
jget(x) | get J noun x |
jloadprofile | load standard J profile |
jlog boolean | log on/off (EXE only) |
jshow boolean | show on/off (EXE only) |
You can customize these or add your own utilities.
Loading J automatically
In the Module, enter an auto_open subroutine as follows:
Sub auto_open()
jxopen
jloadprofile
jshow 1
jlog 1
End Sub
This sub will be run each time this workbook is opened. It opens the JEXEServer, shows the J session and logs commands sent from J.
Now check that auto_open works correctly when you load the book. Switch back to Excel, save the book as test.xls and close Excel - note that the J session will close as well. Reload Excel, and open test.xls - you should see the J session again. Arrange the windows so that both Excel and J are visible.
jcmd
In Excel, switch to Sheet1 and in cell B3 enter:
=jcmd("+/2 3 5 7")
The statement should be executed in J, and the result (17) displayed in Excel.
Try:
In cell B5 enter: 12
In cell B6 enter: 15
In cell B7 enter: =jcmd(B5 & "*" & B6)
B7 displays the result (180). Note that if you now change B5 or B6, then B7 will be recalculated.
In general, jcmd can be used for calculations which return a single value to be displayed in the current cell. The right argument is the sentence to be sent to J.
This method is really only suitable for simple calculations. Typically, you will want to run calculations that return a range of results to Excel and you set up such calculations by invoking an Excel macro explicitly, for example, by selecting Tools|Macro|Macros|Run or pressing an assigned hot-key.
jcmdc, jcmdr
These utilities execute a J expression, displaying the result in a range in the active sheet. Function jcmdc specifies the range as 4 numbers: topleft row, column, number of rows, number of columns. Function jcmdr specifies the range in the traditional alphanumeric notation, for example: C6:E10.
We will create a macro run to test these and subsequent expressions. Switch to the module and enter:
Sub run()
jcmdc "?3 4$10", 2, 3, 3, 4
End Sub
Next, return to the worksheet, select Tools|Macro|Macros, highlight run and click Options. Enter Ctrl-r as the shortcut key and click OK. If J has been closed down, then either reload the spreadsheet or re-run auto_open to load it again. Close the Macro dialog, switch to Sheet1 and press Ctrl-r. The macro should run and display the result. Press Ctrl-r again to re-run the macro.
jsetc, jsetr
These utilities set values in J, from a range in the active sheet. As with jcmdc and jcmdr above, jsetc specifies the range as 4 numbers and function jsetr specifies the range in the traditional notation. Switch to the module and edit run to:
Sub run()
jsetr "Y", "D3:F4"
End Sub
Switch to Sheet1 and as before use Tools|Macro to select Ctrl-r as a shortcut key for the macro. In the worksheet, press Ctrl-r. Then click on the J session and display Y (these are random numbers so the exact values will likely differ):
Y +-+-+-+ |4|8|8| +-+-+-+ |7|3|1| +-+-+-+Change run to:
Sub run()
jsetc "Y", 2, 3, 3, 4
jcmdc "+/\>Y", 7, 3, 3, 4
End Sub
Switch to Sheet1, use Tools|Macro to select Ctrl-r as the shortcut key, then in the worksheet, press Ctrl-r. The macro will read the numbers in the upper range and display the sum scan in the lower range. Now if you change one of the numbers in the upper range, for example E2, press Ctrl-r to update the lower range.