Here is a lisp file I got from David Stein's website when it was up.
This will be your guide to understanding the process of linking with excel.
Excel examples:
(vl-load-com)
;;;*************************************************************************
;;; MODULE: DSX-TypeLib-Excel
;;; DESCRIPTION: Determines which Version of Excel you have
;;;*************************************************************************
(defun DSX-TypeLib-Excel ( / sysdrv tlb)
(setq sysdrv (getenv "systemdrive"))
(cond
( (setq tlb (findfile (strcat sysdrv "\\Program Files\\Microsoft Office\\Office\\Excel8.olb")))
tlb
)
( (setq tlb (findfile (strcat sysdrv "\\Program Files\\Microsoft Office\\Office\\Excel9.olb")))
tlb
)
( (setq tlb (findfile (strcat sysdrv "\\Program Files\\Microsoft Office\\Office\\Excel10.olb")))
tlb
)
( (setq tlb (findfile (strcat sysdrv "\\Program Files\\Microsoft Office\\Office\\Excel.exe")))
tlb
)
( (setq tlb (findfile (strcat sysdrv "\\Program Files\\Microsoft Office\\Office10\\Excel.exe")))
tlb
)
)
)
;;;*************************************************************************
;;; MODULE: DSX-Load-TypeLib-Excel
;;; DESCRIPTION: Loads the Type Library for Excel
;;;*************************************************************************
(defun DSX-Load-TypeLib-Excel ( / tlbfile tlbver out)
(cond
( (null msxl-xl24HourClock)
(if (setq tlbfile (DSX-TypeLib-Excel))
(progn
(setq tlbver (substr (vl-filename-base tlbfile) 6))
(cond
( (= tlbver "9")
(princ "\nInitializing Microsoft Excel 2000...") )
( (= tlbver "8")
(princ "\nInitializing Microsoft Excel 97...") )
( (= (vl-filename-base tlbfile) "Excel.exe")
(princ "\nInitializing Microsoft Excel XP...")
)
)
(vlax-import-type-library
:tlb-filename tlbfile
:methods-prefix "msxl-"
:properties-prefix "msxl-"
:constants-prefix "msxl-"
)
(if msxl-xl24HourClock (setq out T))
)
)
)
( T (setq out T) )
)
out
)
;;; Note: <dmode> is either "SHOW" or "HIDE" depending upon whether
;;; you want the Excel session to be accessible to the user directly.
(defun DSX-Open-Excel-New (dmode / appsession)
(princ "\nCreating new Excel Spreadsheet file...")
(cond
( (setq appsession (vlax-create-object "Excel.Application"))
(vlax-invoke-method (vlax-get-property appsession 'WorkBooks) 'Add)
(if (= (strcase dmode) "SHOW")
(vla-put-visible appsession 1)
(vla-put-visible appsession 0)
)
)
)
appsession
)
;;; Note: <xfile> is the fully-qualified filename to be opened,
;;; <dmode> is either "SHOW" or "HIDE" depending upon whether
;;; you want the Excel session to be accessible to the user directly.
(defun DSX-Open-Excel-Exist (xfile dmode / appsession)
(princ "\nOpening Excel Spreadsheet file...")
(cond
( (setq fn (findfile xfile))
(cond
( (setq appsession (vlax-get-or-create-object "Excel.Application"))
(vlax-invoke-method
(vlax-get-property appsession 'WorkBooks)
'Open fn
)
(if (= (strcase dmode) "SHOW")
(vla-put-visible appsession 1)
(vla-put-visible appsession 0)
)
)
)
)
( T (alert (strcat "\nCannot locate source file: " xfile)) )
)
appsession
)
;;; Get cell object relative to range using <relrow> and <relcol>
(defun DSX-Excel-Get-Cell (rng relrow relcol)
(vlax-variant-value
(msxl-get-item (msxl-get-cells rng)
(vlax-make-variant relrow)
(vlax-make-variant relcol)
)
)
)
;;; Return value of contents in cell (row, col)
(defun DSX-Excel-Get-CellValue (row col)
(vlax-variant-value
(msxl-get-value
(DSX-Excel-Get-Cell
(msxl-get-ActiveSheet xlapp)
row col
)
)
)
)
;;;*************************************************************************
;;; MODULE: DSX-Excel-Get-RowValues
;;; DESCRIPTION: Returns a list of cell values within a given row
;;; ARGS: row-number(int), startcol, num-cells
;;; EXAMPLE: (DSX-Excel-Get-RowValues 3 1 20) get first 20 values in row 3
;;;*************************************************************************
(defun DSX-Excel-Get-RowValues (row startcol numcells / next out)
(setq next startcol)
(repeat numcells
(setq out
(if out
(append out (list (DSX-Excel-Get-CellValue row next))); row x col
(list (DSX-Excel-Get-CellValue row next)); row x col
)
next (1+ next)
)
); repeat
out
)
;;;*************************************************************************
;;; MODULE: DSX-Excel-Get-ColumnValues
;;; DESCRIPTION: Returns a list of cell values within a given column
;;; ARGS: column-number(int), startrow, num-cells
;;; EXAMPLE: (DSX-Excel-Get-ColumnValues 2 1 20) get top-20 entries in column 2 ("B")
;;;*************************************************************************
(defun DSX-Excel-Get-ColumnValues (col startrow numcells / next out)
(setq next startrow)
(repeat numcells
(setq out
(if out
(append out (list (DSX-Excel-Get-CellValue next col)))
(list (DSX-Excel-Get-CellValue next col))
)
next (1+ next)
)
); repeat
out
)
;;;*************************************************************************
;;; MODULE: DSX-Excel-GetRangeValues-ByRows
;;; DESCRIPTION: Get range values in row order and return as nested lists
;;; ARGS: startrow, startcol, num-rows, num-cols
;;; EXAMPLE: (DSX-Excel-GetRangeValues-ByRows 1 1 5 10) get range values from 1A to 5J where each sublist is one row
;;;*************************************************************************
(defun DSX-Excel-GetRangeValues-ByRows (startrow startcol numrows numcols / nextrow rowlst outlst)
(setq nextrow startrow)
(repeat numrows
(setq rowlst (DSX-Excel-Get-RowValues nextrow startcol numcols)
outlst (if outlst (append outlst (list rowlst)) (list rowlst))
nextrow (1+ nextrow)
)
)
outlst
)
;;;*************************************************************************
;;; MODULE: DSX-Excel-GetRangeValues-ByCols
;;; DESCRIPTION: Get range values in column order and return as nested lists
;;; ARGS: startrow, startcol, num-rows, num-cols
;;; EXAMPLE: (DSX-Excel-GetRangeValues-ByCols 1 1 5 10) get range values from 1A to 5J where each sublist is one column
;;;*************************************************************************
(defun DSX-Excel-GetRangeValues-ByCols (startrow startcol numrows numcols / nextrow nextcol collst outlst)
(setq nextcol startcol)
(repeat numcols
(setq collst (DSX-Excel-Get-ColumnValues nextcol startrow numrows)
outlst (if outlst (append outlst (list collst)) (list collst))
nextcol (1+ nextcol)
)
)
outlst
)
;;;*************************************************************************
;;; MODULE: DSX-Excel-Put-ColumnList
;;; DESCRIPTION: Write each list member to a column (startcol) starting at row (startrow)
;;; ARGS: list, startrow, startcol
;;; EXAMPLE: (DSX-Excel-Put-ColumnList '("A" "B" "C") 1 2) puts members into cells (1,B) (2,B) (3,B) respectively
;;;*************************************************************************
(defun DSX-Excel-Put-ColumnList (lst startrow startcol)
(foreach itm lst
(msxl-put-value
(DSX-Excel-Get-Cell range startrow startcol)
itm
)
(setq startrow (1+ startrow))
); repeat
)
;;;*************************************************************************
;;; MODULE: DSX-Excel-Put-RowList
;;; DESCRIPTION: Write each list member to row (startrow) starting at column (startcol)
;;; ARGS: list, startrow, startcol
;;; EXAMPLE: (DSX-Excel-Put-RowList '("A" "B" "C") 2 1) puts members into cells (1,B) (1,C) (1,D) respectively
;;;*************************************************************************
(defun DSX-Excel-Put-RowList (lst startrow startcol)
(foreach itm lst
(msxl-put-value
(DSX-Excel-Get-Cell range startrow startcol)
itm
)
(setq startcol (1+ startcol))
); repeat
)
;;;*************************************************************************
;;; MODULE: DSX-Excel-Put-CellColor
;;; DESCRIPTION: Applies fill-color to specified cell
;;; ARGS: row, column, color (integer)
;;; EXAMPLE: (DSX-Excel-Put-CellColor 1 1 14) apply color #14 to cell (1,A)
;;;*************************************************************************
(defun DSX-Excel-Put-CellColor (row col intcol / rng)
(setq rng (DSX-Excel-Get-Cell (msxl-get-ActiveSheet xlapp) row col))
(msxl-put-colorindex (msxl-get-interior rng) intcol)
)
;;;*************************************************************************
;;; MODULE: DSX-Excel-Put-RowCellsColor
;;; DESCRIPTION: Applies fill-color to a row of cells
;;; ARGS: startrow, startcol, num-cols, color (integer)
;;; EXAMPLE: (DSX-Excel-Put-RowCellsColor 1 1 5 14) Start at row=1 col=1 repeat for 5 columns using color #14
;;;*************************************************************************
(defun DSX-Excel-Put-RowCellsColor (startrow startcol cols intcol / next)
(setq next startcol)
(repeat cols
(DSX-Excel-Put-CellColor startrow next intcol)
(setq next (1+ next))
)
)
;;;*************************************************************************
;;; MODULE: DSX-Excel-Put-ColumnCellsColor
;;; DESCRIPTION: Change fill color in a column of cells
;;; ARGS: startrow, startcol, num-rows, color (integer)
;;; EXAMPLE: (DSX-Excel-Put-ColumnCellsColor 1 1 5 14) Start at row=1 col=1 repeat for 5 rows using color #14
;;;*************************************************************************
(defun DSX-Excel-Put-ColumnCellsColor (startrow startcol rows intcol / next)
(setq next startrow)
(repeat rows
(DSX-Excel-Put-CellColor next startcol intcol)
(setq next (1+ next))
)
)
;;;*************************************************************************
;;; MODULE: DSX-Excel-RangeAutoFit
;;; DESCRIPTION: Applies Auto-Fit to columns within active range
;;; ARGS: active-sheet (object)
;;; EXAMPLE: (DSX-Excel-RangeAutoFit myxlws)
;;;*************************************************************************
(defun DSX-Excel-RangeAutoFit (active-sheet)
(vlax-invoke-method
(vlax-get-property
(vlax-get-property
(vlax-get-property active-sheet 'UsedRange)
'Cells
)
'Columns
)
'AutoFit
)
)
;;;
;;;
;;;
(defun w-Excel-Get-Current-Column (activesheet / widths cnt num activerange)
(setq activerange
(vlax-get-property
(vlax-get-property
(vlax-get-property activesheet 'UsedRange)
'Cells
)
'Columns
)
)
(setq widths '(22 16 14 35))
(setq cnt (msxl-get-count activerange))
(setq num 0)
(repeat cnt
(w-Excel-Put-ColumnWidth activerange (nth num widths) num)
(w-Excel-Put-Borders (msxl-get-Borders activerange) 1)
(setq activerange (msxl-get-next activerange))
(setq num (1+ num))
)
)
;;;;
;;;;
;;;;
(defun w-Excel-Put-ColumnWidth (currentcolumn val num / ind)
(msxl-put-columnwidth
currentcolumn
(vlax-make-variant val vlax-vbDouble)
)
(setq ind (cond ((= num 0) 2)
((= num 1) 3)
((= num 2) 3)
((= num 3) 2)
)
)
(msxl-put-horizontalalignment
(msxl-get-EntireColumn currentcolumn)
(vlax-make-variant ind vlax-vbLong)
)
)
;;;*************************************************************************
;;; MODULE: DSX-Excel-Put-ColumnCellsColor
;;; DESCRIPTION: Change fill color in a column of cells
;;; ARGS: startrow, startcol, num-rows, color (integer)
;;; EXAMPLE: (DSX-Excel-Put-ColumnCellsColor 1 1 5 14) Start at row=1 col=1 repeat for 5 rows using color #14
;;;*************************************************************************
(defun w-Excel-Put-Borders (borders num / newLineStyle newValue)
(setq newLineStyle
(msxl-put-LineStyle
borders
(vlax-make-variant num vlax-vbLong)
)
)
)
;;;*************************************************************************
;;; MODULE: C:GETXLREGION
;;; DESCRIPTION: THIS IS THE MAIN PROGRAM
;;;*************************************************************************
(defun C:GETXLREGION
( / xlapp xlfile ready tlbfile ash range xlist)
(cond
( (DSX-Load-TypeLib-Excel)
(cond
( (setq xlfile
(getfiled "Excel Spreadsheet File"
(if G$XFILE G$XFILE "") "XLS" 8
))
(setq G$XFILE xlfile)
(cond
( (setq xlapp (DSX-Open-Excel-Exist xlfile "HIDE"))
(setq ash (msxl-Get-ActiveSheet xlapp))
(setq range (msxl-Get-ActiveCell xlapp))
;;; fetch data starting at row=2 column=1 and get
;;; the next 68 rows, 6 columns each
(setq xlist (DSX-Excel-GetRangeValues-ByRows 2 1 68 6))
;;; Print out each sublist to see what you got...
(foreach mbr xlist (princ mbr) (terpri))
(setq xlist nil)
(DSX-Excel-Quit xlapp)
(gc); forced garbage collection after closing Excel!
)
( T (princ "\nFailed to start application session.") )
)
)
)
)
( T (alert "Failed to initialize type library for Excel 97...") )
)
(princ)
)
--------------------------------------------------------------------------------
;;;*************************************************************************
;;; MODULE: DSX-Excel-Quit
;;; DESCRIPTION: Quit and close Excel session (app)
;;; ARGS: app (session object)
;;; EXAMPLE: (DSX-Excel-Quit xlapp)
;;;*************************************************************************
(defun DSX-Excel-Quit (appsession)
(cond
( (not (vlax-object-released-p appsession))
(vlax-invoke-method appsession 'QUIT)
(vlax-release-object appsession)
)
)
)
;;;*************************************************************************
;;; MODULE: DSX-Excel-Kill
;;; DESCRIPTION: Forces any open Excel sessions to be closed
;;; ARGS: none
;;; EXAMPLE: (DSX-Excel-Kill)
;;;*************************************************************************
(defun DSX-Excel-Kill ( / eo)
(while (setq eo (vlax-get-object "Excel.Application"))
(DSX-Excel-Quit eo)
(vlax-release-object eo)
(setq eo nil)
(gc)(gc);; even this doesn't always kill the damn thing!
)
)
