Current issue

Vol.26 No.4

Vol.26 No.4

Volumes

© 1984-2017
British APL Association
All rights reserved.

Archive articles posted online on request: ask the archivist.

archive/21/3

Volume 21, No.3

This article might contain pre-Unicode character-mapped APL code.
See here for details.

Master and Slave:
Dyalog APL Directs Excel!

by Kai Jger

Introduction

As APLers, using an application from Microsofts Office Suite like Excel or Word as an OLE object makes sense in some cases. For example, printing under Windows can be a real nightmare!

Because APL has a great strength in dealing with tables, one often wishes to print them. Since Excel offers great features and flexibility for doing this, why should we not use it for this task? One possible way to do this is to use the Excel OLE object from within APL.

If you cant beat them, join them!

This article will look into how to: start Excel; load a specified spreadsheet; get some or all of the data into (or out of) your workspace; and use Excel as a printing machine from APL. Also, to try to make this useful for APLers with no or little experience of using OLE objects, I will show some typical techniques to get information about a specific OLE objects.

This article is not addressed to experienced programmers, and doesnt assume you are familiar with MSDN and Visual Studio.

A typical example of what we want to do is to call a command (say via a context menu) that exports selected rows from a grid in an APL GUI application out to Excel.

Starting Excel

The first thing to do is to start Excel as an OLEclient from APL:

'MyOleObj'ŒWC'OLEClient'
'Excel.Application' 

If Excel was already running on your PC, thats fine. If Excel was not already running, nothing will seem to happen, but Excel will now be running in the background. This is because the Excel GUI is not displayed by default. We can expressly ask Excel to display:

MyOleObj.Visible„1 

Now lets set the Caption of the Excel main window:

MyOleObj.Caption„'APL
and Excel' 

Next we need to create a brand new workbook, since without such a workbook, nothing could happen in Excel:

'MyOleObj.MyWorkbook'ŒWC
MyOleObj.Workbooks

“MyWorkbook need not necessarily be a child of MyOleObj, since there is no hierarchical dependency but why not?

Workbooks and Templates

Do you wish to use a specific template? You probably know templates from Word (the *.dot-files); in Excel a corresponding mechanism is also available. An Excel template is a document with the file extension xlt. If your template is not held in Excels default template directory, make sure you specify the full path name:

#.MySheets„MyOleObj.MyWorkbook.Open'Standard.xlt'

If you are not interested in using a template, use the method Add instead:

#.MySheets„MyOleObj.MyWorkbook.Add

Worksheets

We can deal now with the newly-created workbook, which by default contains 3 sheets. Lets create a new object that represents one of the sheets in our workbook. Because we have just created this workbook, it is by definition the active one, so we can deal with the ActiveSheet:

#.MySheet„#.MySheets.ActiveSheet

OK, thats fine so far. For the next step we will insert some data and column titles into the sheet. First lets create two variables, one holding the column titles, the other one is needed to represent our table:

ColTitles„'The Very First Lastname' 'Firstname' 'Street' 'Code'  'Town'
Values„0 5''
Values„'Jger' 'Kai' 'Thisstreet 1' 9999 'Nuremberg'
Values„'Taylor' 'Stephen' 'Thatstreet 2' 123456 'London'

The next step will be to insert the column titles into our sheet. We will need to specify starting and ending points. The starting point is (1,1), and the end point is (1,5) since we have 5 columns. (Excel works in origin 1). What is a bit strange is that the two points defining the range are Objects of type Range.

Visual Basic and Why it is Helpful

Lets introduce now a good way to get information about OLE objects, methods and properties. Make Excel the topmost window and then press Alt+F11. This should open the Visual Basic for Applications IDE. If not, it is perhaps not installed as part of Office on your computer; but you can easily install this stuff.

Now you have access to the Visual Basic Help, a valuable source about the OLE object model of Excel! For example, enter the word Range into the Search help field. Then select Range (List) (or similar) from the list.

Syntax Problems: Default Methods

In the Help window you will find valuable information on how to deal with Range. If you look around the help information, you will find statements similar to:

      Worksheets(1).Cells(1, 1).Value = 24

The problem is that this wont work in APL as it stands! This is because in Visual Basic, for example, some objects have a default method. If a named object with a default method (for example Worksheets) is referenced without specifying a method, the default method is called. There is no such mechanism available in APL, so we need to call the default method explicitly. The default method for Worksheets is Item, so the statement translated into APL would be:

((MyOleObj.Worksheets.Item 1).Cells.Item 1 1).Value2 „24

Note that here instead of Value the name Value2 is used we will come back to this later!

Arranging a Range

Lets define two objects of type Range, useful as start and end points:

StartPoint„MySheet.Cells.Item(1 1)
EndPoint„MySheet.Cells.Item(1,ColTitles)

Accessing Data: Methods and Properties

Now we can reference Value, but be careful: it is not a property, it is a method. This method does not use an argument (in documentation it is specified as void). From APL a void argument is supplied as an empty numeric vector:

      (MySheet.Range(StartPoint EndPoint)).Value 
24 [Null] [Null] [Null] [Null]

So we get back 24, together with some NULLs!

With the statement:

(MySheet.Range(StartPoint EndPoint)).Value2„ColTitles

we can assign the 5 cells in the first row in one single step.

You can check the cells by calling the appropriate method Value, but dont forget the right argument; otherwise you get a pointer to the method instead of the contents of the cells:

(MySheet.Range StartPoint EndPoint).Value 

As you may have noticed, here is another difference between Visual Basic and APL! In Visual Basic you use Value for both, referencing and specifying the contents of a certain cell. In APL, you need to call the method Value with a right argument to reference the cell contents, while you need to specify the Value2 property to change the cell contents.

Specifying Columns and Data

Lets insert the real data into the Excel sheet now. Again we need a start point and an end point. The start point is (2,1), the end point is 1 0 + the number of rows and cells.

StartPoint„MySheet.Cells.Item(2,1)
EndPoint„MySheet.Cells.Item(1 0+Values)

And now we can set the real data:

(MySheet.Range(StartPoint EndPoint)).Value2„Values

Changing Column Width

Currently all the columns are of the standard width. We should now tell Excel that we want the column width to fit the data. Columns.AutoFit is a method of the Range object, and it is therefore no surprise, that only the contents of the cells within that range are taken into consideration. This means that executing the statement:

(MySheet.Range(StartPoint EndPoint)).Columns.AutoFit

ignores the width of the cells in the first row, since this row is not part of the Range object we are currently using. Furthermore, (with some versions of Excel) an annoying result is printed in the session manager. Fortunately, both problems could be solved relatively easily. Instead of our own Range object, we can use the built-in object UsedRange. This Range includes at least all the cells that contain a value. At least because maybe there are some empty rows or columns embedded within the data, but this is unimportant here,

To suppress any annoying result, we can simply use an empty dynamic function:

{}MySheet.UsedRange.Columns.AutoFit

Thats it! Easy, isnt it?

Suppressing Useless Warnings

Sometimes, Excel displays some really annoying dialogue boxes. A typical example is the This macro has an invalid signature message box. If you need to make sure that no such dialogue box interrupts you, simply set:

MyOleObj.DisplayAlerts„0

But when you have finished, dont forget to set this back to 1!

Excel as Printer Server

We started wanting to use Excel as a printing machine, and in fact we are able to do so by automating really everything. For such a task, you would not of course want to set

MyOleObj.Visible„1

The PageSetup Object

Before executing the print command, maybe you want to specify a number of parameters important for print tasks. For this we need a PageSetup object:

MyPageSetup„MySheet.PageSetup

Specifying a Header

Now we can specify a centred header:

MyPageSetup.CenterHeader„'APL and Excel'

...or a left header:

MyPageSetup.LeftHeader„'Stephen Taylor'

...or a right header:

MyPageSetup.RightHeader„'Kai Jäger'

...or a footer which should be centred:

MyPageSetup.CenterFooter„'Written for Vector'

...or a left footer:

MyPageSetup.LeftFooter„'Page 1'

...or a right footer:

MyPageSetup.RightFooter„'2004-12-26'

Miscellaneous Print Properties

Maybe you do not want to print the whole spreadsheet but only part of it. For this, we again need a Range object:

StartPoint„MySheet.Cells.Item(1 1)
EndPoint„MySheet.Cells.Item(1 0+Values)
MyRange„MySheet.Range(StartPoint EndPoint)
MyPageSetup.PrintArea„MyRange.AddressLocal 
MyPageSetup.CenterHorizontally„0
MyPageSetup.CenterVertically„1

Display Print Results

To display the result, we need to call the method PrintPreview. This method also does not use an argument:

MySheet.PrintPreview 

Got a DOMAIN ERROR?! We come back to this shortly.

Printing

To print the sheet, merely call the method PrintOut (again with an empty vector as right argument).

MySheet.PrintOut 

Coming to an End

If you want save the newly created spreadsheet, simply call the method SaveAs:

MySheet.SaveAs 'MyFilename.xml'

And finally we want quit Excel:

MyOleObj.Quit

Reading a Spreadsheet from Disk

Finally you may have a problem very close to what we discussed so far:

You (that is, your APL program) knows about an existing spreadsheet, or at least you expect there is such a spreadsheet, and now you want to read this data into your workspace. Of course you need the name and the folder where the spreadsheet can be found, but you prompt your user for that.

There are different ways to read the data from a spreadsheet into APL: you can use DDE (which is a wobbly thing); or you can use ODBC; or you can use Excel via OLE. Although the ODBC solution has some advantages (Excel is not needed, and its fast), we will only discuss the OLE solution here.

We already know how to load a specific file into Excel:

file„'C:\My Documents\My Spreadsheet.xls'
MyOleObj.MyWorkbook.Sheet„MyOleObj.MyWorkbook.Open file

As you can see, the Open method is able to load not only a template but also an ordinary spreadsheet.

Again: the Range Object

Again the UsedRange object could be used to access all data in the current worksheet. This is fine since we dont need to know anything about the data contained in that spreadsheet:

MyRange„MyOleObj.ActiveSheet.UsedRange
Data„MyRange.Value 

Thats it!

Valuable Sources of Information

So far we can do some useful things, but for a more complex task we need a general overview over the Excel OLE object model, of course.

This information is available on the MDSN pages on the web by Microsoft. The link is quite easy to remember:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_wrcore/html/wrconExcelObjectModelOverview.asp

However, maybe you are interested in an alternative way: enter http://msdn.Microsoft.com into the browsers address field and then the words <excel object model> into the search field. Select http://msdn.microsoft.com/library/en-us/dv_wrcore/html/ wrconExcelObjectModelOverview.asp from the result list.

Hints and Tips

Note that:

  • there may or may not be some empty rows and columns around the real data
  • some cells may contain a NULL
  • Dates are returned as numeric vectors of length 7 like ŒTS!

To find out which cells are empty, use ŒNULL:

DataŒNULL

If you need to read the data from the second sheet of a workbook instead of the first one:

(MyOleObj.Sheets.Item 2).UsedRange.Value 

If there is no such sheet available, because that file consists of one sheet only, you will get a DOMAIN ERROR here.

However, this technique may be dangerous: another user may have moved the second worksheet to be the third one now. So it is maybe a better idea to use the name of the sheet you are interested in. For example, if you know that this worksheet is named ABC, you can do this:

(MyOleObj.Sheets.Item'ABC').UsedRange.Value 

And of course you can use this syntax, too:

MySheet„MyOleObj.Sheets.Item'ABC'
MySheet.UsedRange.Value 

A note on APL syntax:

MySheet.Range(StartPoint EndPoint).AddressLocal 

gives a Length Error but

MySheet.Range(StartPoint EndPoint)).AddressLocal 

works just fine.

The F1 key in APL

If Visual Basic for Applications is installed together with Office, entering the name of an OLE item (object, property, method or event) and then pressing F1, should display a proper help file. However, on some but not all machines that does not work. Instead a message box is displayed stating that the help file could not be found:

screenshot

(The exact file name and location will vary depending on the versions of Office and Windows running on your PC.)

We found that a common reason for this is that the file VBAXL10.chm is not found in C:\Programme\Microsoft Office\Office11 but in C:\Programme\Microsoft Office\Office11\1031\VBAXL10.CHM instead.

This is really annoying! One possible solution is to simply copy the help file from C:\Programme\Microsoft Office\Office11\1031 to C:\Programme\Microsoft Office\Office11 (UK and US readers should look in Program Files).

Visual Basic Macros

A final suggestion. Sometimes you might need to do something but are unable to find out how from the documentation. In such a case it may be helpful to use a Visual Basic Macro: start Recording Macro within Excel, perform the needed actions, stop recording and then take a look into the Visual Basic code. Although there are some differences between Visual Basic and APL, this may still be helpful.

Interruptions and Trapping

If you need to use OLE automation within a real application, be aware that sometimes astonishing things may happen. Just because a certain operation works well 20 or 30 times does not mean that it will run at every time! Sometimes there are DOMAIN ERRORs or the like, that you are not able to reproduce or to explain, and of course this is really bad news. However, you will get a feeling for how to deal with the code over the time. Adding some explicit :Trap statements might be a good idea here, but you should avoid :Trap 0.

For example: the user decides to print" a document with the Microsoft Document Image Writer Office 2003 comes with. Then the user gets a Save File" dialog, because this printer driver does not print but create a file you may display with a viewer. If for any reason the user then presses the Cancelbutton in this SaveDialog box, you get a DOMAIN ERROR in APL!

Therefore you should not use

MySheet.PrintOut 

but:

:Trap 11  MySheet.PrintOut   :EndTrap

Thanks to Ray Cannon for help in editing this article.


script began 9:54:51
caching off
debug mode off
cache time 3600 sec
indmtime not found in cache
cached index is fresh
recompiling index.xml
index compiled in 0.2615 secs
read index
read issues/index.xml
identified 26 volumes, 101 issues
array (
  'id' => '10004060',
)
regenerated static HTML
article source is 'HTML'
source file encoding is 'UTF-8'
URL: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_wrcore/html/wrconexcelobjectmodeloverview.asp => http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_wrcore/html/wrconExcelObjectModelOverview.asp
URL: http://msdn.microsoft.com => http://msdn.Microsoft.com
URL: http://msdn.microsoft.com/library/en-us/dv_wrcore/html/wrconexcelobjectmodeloverview.asp => http://msdn.microsoft.com/library/en-us/dv_wrcore/html/wrconExcelObjectModelOverview.asp
URL: kaipic01.png => trad/v213/kaipic01.png
completed in 0.2884 secs