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:

• Excel Application
      The host Excel application is the top container for all Excel ActiveX Objects.
• Workbooks, Workbook and Worksheet
      Each .XLS file was called as workbook in Excel. The collection of workbooks will be the workbooks because we can open many .XLS file within one Excel session. Normally, a workbook can contain one or more worksheets.
• Range
      Cells in worksheet are called as "Range", the "Range" object can be one single cell, some cells or even all cells in the worksheet.
  
      Each range has a Cell ID string, if it is a single cell, the Cell ID may be a string like "B12", if the range contain so many cells or the Range is a single but merged cell, the Cell ID will be the combination of the LeftTop and RightBotton Single cell ID such  as "B1:F12".

Step1: Catch or create Excel session

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

• (vlax-get-object Prog-id)
      Creates a new instance of an application object.
• (vlax-create-object Prog-id)
      Returns a running instance of an application object.
• (vlax-get-or-create-object Prog-id)
      Returns a running instance of an application object, or creates a new instance, if the application is not currently running.

    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.

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:

• ActiveCell
  If no workbook is opened, return NIL, or return the current active cell [Range VLA-OBJECT].
• ActiveSheet
  if no workbook is opened, return NIL, or return current worksheet
• ActiveWorkbook
  if no workbook is opened, return NIL, or return current workbook
• Range
  Range in Excel session. Extra argument(s) will be required to get the Range Object. In Visual LISP codes, we can use the following two ways:
  • Row and Column number ID;
    (vlax-get-property xl "Range" StartRow StartCol EndRow EndCol)
    eg: (vlax-get-property xl "Range" 1 1 5 3)
  • Cell ID.
    (vlax-get-property xl "Range" CellIDString)
    eg: (vlax-get-property xl "Range" "A1:C5")
• Selection
  User selected cell(s) in Excel
• Visible
  :vlax-false    The Excel session running hidden
  :vlax-true    Display the Excel session
      When we create a new Excel session by calling vlax-get-or-create function and return the Excel Application, the Excel is still invisible, to make it visible, we must set the "Visible" property to :vlax-true.
• Workbooks
  The collection of all workbooks in Excel session

Methods:

• Quit()
  VBA Resource: Quit()
  Close the Excel session.
      There is a limitation using this method: the excel session will also be in memory. We must use the Windows TaskManager to terminate the EXCEL.EXE.

    The workbook contain the following important properties and methods:

Properties:

• ActiveSheet
  if no workbook is opened, return NIL, or return current worksheet
• Saved
  :vlax-false    The workbook is not saved
  :vlax-true      The workbook is saved
• Sheets
  The sheet objects collection in workbook. By discovering the Sheets, we can get names of all sheets.
  (vlax-for sheet sheets
      (setq Rtn (cons (vlax-get-property sheet "Name") Rtn))
  )

Methods:

• Close(3)
  VBA Resource: Close([SaveChanges], [Filename], [RouteWorkbook])
  Close the Excel workbook.
  If the 1st argument is :vlax-:false for not saving, the following two argument will both be ignored, so they are not needed.
  If the 1st argument is :vlax-:true for saving, the saved filename should be indicated in codes.
• MergeWorkbook(1)
  VBA Resource: MergeWorkbook(Filename)
  Merge a .XLS with current workbook.
• NewWindow()
  VBA Resource: NewWindow()
  Create a new Excel window. (Book1 will be named as Book1:1 and new window will be labeled as Book1:2)
• Save()
  VBA Resource: Save()
  Save current workbook.

    The worksheet contain the following important properties and methods:

Properties:

• Cells
  All cells in worksheet.
• Name
  Name of worksheet.
• UsedRange
  All used cells. Please notice that this may not be the cells with contents, in fact, empty cells within the used cells' "rectangle" area will also be included in return value.
• Sheets
  The sheet objects collection in workbook

Methods:

• Delete()
  VBA Resource: Sub Delete()
  Delete the worksheet from workbook.
• Move(2)
  VBA Resource: Sub Move([Before], [After])
  Move the sheet among the sheets. The Before and After are both the sheet objects or 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
• Column
  The column id of start cell
• Columns
  The column cells object collection
• ColumnWidth
  The column width
• EntireColumn
  The entire column object
• EntireRow
  The entire row object
• Font
  The content font object
  • Background
  • Bold
  • Color
  • ColorIndex
  • FontStyle
  • Italic
  • Name
  • OutlineFont
  • Shadow
  • Size
  • Strikethrough
  • Subscript
  • Superscript
  • Underline
    The font underline style
    VBA Resource: Const xlUnderlineStyleDouble = -4119 (&HFFFFEFE9)
                                   Const xlUnderlineStyleDoubleAccounting = 5
                                   Const xlUnderlineStyleNone = -4142 (&HFFFFEFD2)
                                   Const xlUnderlineStyleSingle = 2
                                   Const xlUnderlineStyleSingleAccounting = 4
• HasFormula
  If the cell has formula
• Height
  The cell height
• HorizontalAlignment
  The horizontal alignment for cell content
  VBA Resource: Const xlHAlignCenter = -4108 (&HFFFFEFF4)
                                 Const xlHAlignCenterAcrossSelection = 7
                                 Const xlHAlignDistributed = -4117 (&HFFFFEFEB)
                                 Const xlHAlignFill = 5
                                 Const xlHAlignGeneral = 1
                                 Const xlHAlignJustify = -4130 (&HFFFFEFDE)
                                 Const xlHAlignLeft = -4131 (&HFFFFEFDD)
                                 Const xlHAlignRight = -4152 (&HFFFFEFC8)
• Interior
  The cell background object
• Left
  The X position of start cell left-top point from worksheet original
• MergeArea
  The cell border object collection
• MergeCells
  The cell border object collection
• Row
  The row id of start cell
• RowHeight
  The row height
• Rows
  The row cells object collection
• Text
  The cell content text string
• Top
  The Y position of start cell left-top point from worksheet original
• Value2
  The displayed content
• VerticalAlignment
  The vertical alignment for cell content
  VBA Resource: Const xlVAlignBottom = -4107 (&HFFFFEFF5)
                                 Const xlVAlignCenter = -4108 (&HFFFFEFF4)
                                 Const xlVAlignDistributed = -4117 (&HFFFFEFEB)
                                 Const xlVAlignJustify = -4130 (&HFFFFEFDE)
                                 Const xlVAlignTop = -4160 (&HFFFFEFC0)
• Width
  The cell border object collection

Borders
The cell border object collection

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