VisualLISP-EXCEL BIBLE

Control Excel by Visual LISP

by Koz Jono Yeoh
Not finished yet

Excel ActiveX Object Model in Visual LISP

    There are three very important Excel ActiveX Object we should know to use for controlling Excel from within Visual LISP:

Step1: Catch or create Excel session

    The three Visual LISP functions to catch other application objects support ActiveX:

    The Prog-id for MS Excel is "Excel.Application". So we can use (setq xl (vlax-get-or-create-object "Excel.Application")) to get an existing opened Excel session or automatically create a new Excel session if there is not an active Excel session in most situations.

Our codes:
(setq xl (vlax-get-or-create-object "Excel.Application"))

Step1.5: Secrets on detail information about Excel ActiveX Object

    The whole information about Excel ActiveX Object properties and methods can be found via Object Explorer in Excel Visual Basic Editor [Press "F2" to start in Visual Basic Editor].

Step2: Important properties and methods of the Excel session and workbook

    The whole properties and methods of an existing VLA_OBJECT can be get by calling vlax-dump-object (See detail on AutoCAD help documents). For the Excel session VLA-OBJECT, here are the important properties and methods we will use:

Properties:

Methods:


    The workbook contain the following important properties and methods:

Properties:

Methods:


    The worksheet contain the following important properties and methods:

Properties:

Methods:

Our codes:
;Start Excel session
(setq xl (vlax-get-or-create-object "Excel.Application"))
(vlax-put-property xl "Visible" :vlax-true)
(if (null (vlax-get-property xl "ActiveSheet"))
  (vlax-invoke-method (vlax-get-property xl "WorkBooks") "Add")
)
;Processing main data procedure <discuss later>
......
(if (vlax-get-property xl "ActiveWorkbook")(progn
;Not save workbook and close
  (vlax-invoke-method (vlax-get-property xl "ActiveWorkbook") "Close" :vlax-False)
;Save workbook and close
  (vlax-invoke-method (vlax-get-property xl "ActiveWorkbook") "Close" :vlax-True "C:/Sample.XLS")
;Close the Excel session
  (vlax-invoke-method xl "Quit")
  (vlax-release-object xl)
  (setq xl nil)
))

Step3: Discover the RANGE

    The RANGE is the most important object because it is the container of all data in Excel. The RANGE properties and methods are as following:

Properties:

Borders
The cell border object collection

กก

กก
กก

Copyright(C) 1994-2008 by KozMos Inc.
Koz Jono Yeoh | Beijing, PRC