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.
When you create a new book in Excel, by default the book has 16 worksheets. Since we will experiment with adding worksheets under program control, we suggest that you change the default to 1 worksheet (use the Tools|Options|General dialog).
Introduction
In theory, any Excel function can be called directly from J. In practice, some Excel functions have an unusual syntax that is either awkward or impossible to call from J, for example the ChartWizard method. However, you can always get around this by creating a corresponding Excel macro that you can call from J. Moreover it makes sense to use Excel macros anyway - there is no point in trying to duplicate in J a series of Excel function calls that could be just as easily, or more easily, programmed in Excel.
Thus you typically program with a mixture of J functions and Excel macros.
Excel Hierarchy
The various parts of Excel such as the Workbooks, Worksheets and Charts (all known as objects) are organized in a hierarchy. Objects have methods (functions) and properties (variables). References to Excel objects, methods and properties must include their position in the hierarchy, for example:
Application.Workbooks("Book1").Worksheets("MySheet").ChartObjects.Item(1).Chart.PlotArea.Width
Now this naming convention gets a little tedious to enter, so Excel allows you to simplify it a little. For example, if MySheet happens to be the active sheet, you could instead use:
Activesheet.ChartObjects.Item(1).Chart.PlotArea.Width
J does not support this method of referencing names. Instead, for each reference you provide two names - the first being the position in the object hierarchy. Thus if the name abc represented Activesheet.ChartObjects.Item(1).Chart.PlotArea then the equivalent J reference would be:
abc widthHow do you assign names in J to positions in the object hierarchy? To start off with, there are two reserved names. The name
base represents the root of the hierarchy, equivalent to Application in Excel. Thus the following are equivalent:
base visible |
J |
Application.Visible | Excel |
The name temp is assigned to the current position in the hierarchy. For example, if you have just created a new worksheet Sheet1, then the following are equivalent:
temp activate |
J |
|
Worksheets("Sheet1").Activate |
Excel |
Next, at any point, you can assign a name to the temp position. Thus if you assigned the name sh1 to temp at this point, you could then use:
sh1 activateThe idea is that you assign names to positions that you expect to revisit, while
temp can be used for positions that you are just passing through.
Note that Excel names are not case-sensitive, but when programming, Excel automatically converts your entries to its standard capitalization. From J, you can use any case, and 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 J may change the active focus to Excel. To enter new commands in J, click on the J session to change the active focus back to J.
If the OLE link goes wrong somewhere, you can simply close down the Excel session, and reset the J session. The J OLE interface uses the Window Driver, so you should enter wd'reset' to reset it. Of course, normally you would shut down Excel and reset J under program control.
Sometimes you send a command to Excel that appears to hang up, while the Excel session flashes. This happens when Excel displays a dialog box that requires user intervention, for example an error message or a prompt to save changes on exit. In such cases, switch to Excel and respond to the dialog box before continuing.
At other times, Excel will hang up when it is waiting for user entry to be completed. For example, if you highlight a cell and start editing its contents, then switch to J and try an OLE command, the system will hang until you go back to Excel and complete the cell editing.
While Excel is fairly efficient at the tasks you are likely to use it for, you might inadvertently give it a task that takes a long time. For example, suppose you create a chart from data in a spreadsheet, then send a command from J to update that data. After each cell is updated, Excel will re-draw the chart - as many times as there are cells! While this happens, everything is locked up, and you will have to wait, or shut Excel down. (This particular problem is solved by erasing the chart before you update the data, then re-creating it after the update.)
Tutorial
Start by unloading all applications, then loading J. Maximize the ijx window in the J session, then arrange the J session window so it covers only about half the screen.
Opening up Excel
Create a parent to hold the Excel OLE Automation control:
wd 'pc xlauto'
Create the Excel OLE Automation control. This may take a few seconds, because it loads Excel into memory (it will not be visible).
wd 'cc xl oleautomation:excel.application'
All J OLE commands from now on will refer to the xl control. Note that the names xlauto and xl used here are not required - you can use your own names. However, the utilities included with J also use these names, so it is recommended that you stick with them. Note also that xl is short for Excel and not `x',`one'.
At this point, Excel has been loaded, but is not visible. Excel has a Visible property that can be set to display it. This property is part of the Application object, and hence the Excel call to use it would be:
Application.Visible = 1
In J, the Application object is named base, therefore to set it, enter:
wd 'oleset xl base visible 1'
This means: execute oleset on control xl, setting the visible property of base to 1.
If Excel opens full screen, shrink it down so that both the J and Excel windows are visible.
Now Excel is visible, but has no workbook open. To create a new workbook in Excel, you use the Add method of the Workbooks object. Note that Add is a method of the Workbooks object (as well as several other objects), but is it not a method of the Application object - Application.Add will not work! Therefore the first step in J is to get access to the Workbooks object. To do so, enter:
wd 'oleget xl base workbooks'
This command should complete successfully, but display no result. However, internally, J has assigned the Workbooks object to temp, and this can now be used to invoke the Add method:
wd 'olemethod xl temp add'
This should have created a new workbook. Try entering it again to add another workbook:
wd 'olemethod xl temp add' |domain error | wd'olemethod xl temp add'
This time you get a domain error - try: wd'qer'. What happened is that the temp name really is temporary - it refers to the current position in the Excel hierarchy, which is constantly changing as you move about Excel. In this case, when you added the new workbook, temp changed to that workbook - which does not have an Add method!
Therefore, in order to add another workbook, you have to assign temp to Workbooks again:
wd 'oleget xl base workbooks' wd 'olemethod xl temp add'
Of course, this quickly becomes tedious - therefore the proper treatment here is to assign a name to the Workbooks object, so that you can just use that name in future. To do so, use the oleid command:
wd 'oleget xl base workbooks' wd 'oleid xl wb'
Now you can use wb to create several books:
wd 'olemethod xl wb add' wd 'olemethod xl wb add' wd 'olemethod xl wb add'
Closing Excel
Now lets try closing down Excel. The Application object in Excel has the Quit method to close down. Quit will prompt, should there be any unsaved changes. Try switching to Excel, then entering some values into one of the spreadsheets. Ensure that you have completed your entries (press Enter if Excel is waiting for you to complete the entry of a cell), then switch back to J and enter:
wd 'olemethod xl base quit'
Excel will start flashing, and if you try to enter anything in the J window, it eventually displays a "Server Busy" dialog box. Click on Excel, and respond to the "Save changes in `Book'?" prompt. Eventually, Excel will close. You should now reset the J Window Driver with:
wd'reset'
Utilities
This is a good time to look at the Excel OLE utilities, to do so enter:
load 'system\examples\ole\excel\xlutil.ijs' names ''
This defines several utilities, the main ones being:
xlopen | create Excel OLE automation object |
xlshow | show/hide Excel OLE automation object |
xlexit | exit Excel OLE automation object (saves) |
xlget | cover for oleget - get object |
xlset | cover for oleset - set object parameter |
xlcmd | cover for olemethod - invoke method |
xlid | cover for oleid - assign id to current position |
xlread | read cell |
xlreadr | read range |
xlwrite | write cell |
xlwriter | write range |
xlsetchart | set chart range |
The verb xlopen opens up Excel. It:
xlautoxlwb as the Workbooks objectTry:
xlopen''
Note that Excel is not shown, indeed you may want to use Excel without it ever being visible. To make it visible, enter:
xlshow''
Verb xlcmd runs an OLE method. Since wb has been named in xlopen, it can be used directly. To add a workbook:
xlcmd 'wb add'
Take a look at the workbook name:
xlget 'temp name' Book1
Try changing the workbook name:
xlset 'temp name Mybook' |domain error | xlset'temp name Mybook' wd 'qer' ole - Workbook does not have writeable Name property : 12
What is happening is that in Excel, you can only change the name of a workbook by saving it. Thus, the following saves the workbook, and also renames it:
xlcmd 'temp saveas Mybook'
This may return -1, which really is the result from Excel!
(If you already have saved Mybook, Excel will prompt you to overwrite it.)
Accessing the Worksheet
To access the worksheet, we first have to get the Worksheets object, which belongs to the workbook. We will use the Worksheets object a few times, so will give it a name:
xlget 'temp worksheets' xlid 'ws'
We can try adding new worksheets:
xlcmd 'ws add' xlcmd 'ws add' xlcmd 'ws add'
Next we access the first sheet using the Item method, and assign the name sh1:
xlget 'ws item sheet1' xlid 'sh1' xlget 'sh1 name' Sheet1
Be careful to distinguish sh1 which is the name used by J for a position in the Excel object hierarchy, from Sheet1, which is the name used by Excel for the current worksheet. You can change the worksheet name:
xlset 'sh1 name Mysheet'
If this worksheet is hidden behind another (which will be the case if you followed the above steps exactly) you can activate it with:
xlcmd 'sh1 activate'
Now lets try writing to a specific cell. In Excel you can use cell references of the form 2 3 or old-style alphanumeric references such as B3; the former are easier to program. First reference a cell, using the Cells property:
xlget 'sh1 cells 2 3'
Then set the value of temp as required. The new value should appear in the spreadsheet:
xlset 'temp value 123'
xlget 'temp value' 123
Reading and Writing Ranges
In practice, you will typically want to read and write a range of cells. It would be tedious to do so one cell at a time; unfortunately, the form in which Excel reads and writes range data is not available to J. The solution is to use the utilities xlreadr (read range) and xlwriter (write range) that call appropriate macros from jmacros.xls. The right argument is the workbook, worksheet, topleft cell position and number of rows and columns. The left argument of xlwriter is the data to be written. Try:
(i.3 4) xlwriter 'mybook.xls mysheet 2 2'
Verb xlreadr returns data as a boxed array of character strings:
xlreadr 'mybook.xls mysheet 3 3 2 3' +-+--+--+ |5|6 |7 | +-+--+--+ |9|10|11| +-+--+--+
". &> xlreadr 'mybook.xls mysheet 3 3 2 3' 5 6 7 9 10 11
Finally, use xlexit to close Excel (you may be prompted to save):
xlexit''
Data passing
Data parameters sent using these utilities are limited to 65K, which suffices for most purposes. The best way to pass data longer than this is via a temporary file. Thus J can write a file then send an OLE command to Excel to read it.