This tutorial has been created with Excel 97 and J401. Expect different behavior with other versions!
It will help to have the Excel VBA help file VBAXL8.HLP (or similar) readily available.
Before running it, copy the file jsutils.xls from J directory system\examples\ole\excel to your Excel directory.
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.
If you use the utilities in jsutil.xls, there will be no problem in trying to re-open the J OLE Automation object even if it is already open.
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...), 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; also, if you use the jsutil.xls utilities described below, this reference will be done for you.
In the module sheet, enter:
Public js As Object
Sub jopen()
Set js = CreateObject("jexeserver")
End Sub
The first statement declares the name js that will be used for the JEXEServer. 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.
Loading J
Next open up the Immediate window for experimentation (if not already open). To do so, select menu item View|Immediate. 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 (it will not be visible), enter:
jopen
To show J, enter:
js.show 1
This means: run the show method of js, i.e. of the JEXEServer, with argument 1.
The J OLE Automation Server should now be visible. Maximize the ijx window within the J session, then 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.
In the Excel Immediate window, experiment with show:
js.show 0 |
this hides the window |
js.show 1 | this shows it again |
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"
js.do " 1!:1<'c:\autoexec.bat' "
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 close the Immediate window and 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 jsutil.xls.
Close Excel (no save) and the J OLE Automation server. Reload Excel and open a new workbook if none is shown. Use Visual Basic, Insert|Module to create a new module as before, then select Tools|Reference and add jsutil.xls (you will likely have to Browse to the file).
Note that jsutil.xls should already reference the J Servers, so you do not need to reference them specifically in any workbook that includes this as a module. You might confirm that jsutil.xls does indeed reference the J Servers.
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
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.
In practice, only the first command jxopen is needed, and if you are using the JDLLServer instead, you would need only:
Sub auto_open() (do not enter this now!)
jdopen
End Sub
Now put the cursor on the name auto_open and press F5 to run it. The J session should display.
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")
(You may find it more convenient to enter this in the Formula Bar, rather than directly in the cell.)
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|Run or pressing an assigned hot-key, or else by setting the OnEntry property for the worksheet.
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, highlight run and click Options. Enter Ctrl-r as the shortcut key and click OK. 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| +-+-+-+OnEntry
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, you must press Ctrl-r to update the lower range. To get Excel to update the lower range automatically, create and run a function that sets the OnEntry property. In the module, enter:
Sub setentry()
Worksheets("sheet1").OnEntry = "run"
End Sub
Put the cursor on the function name setentry and press F5 to run it. Switch back to sheet1 and try changing values in the upper range - the lower range will be automatically re-calculated.
JDLL
The commands described work the same way using the JDLL. To experiment, switch to the module, comment out auto_open and enter a new version as:
Sub auto_open()
jdopen
setentry
End Sub
Next close Excel (saving changes), re-open it and load the test.xls workbook. Switch to Sheet1 and try changing values in the upper range.