Accessing Microsoft Excel with Visual LISP

One of the most useful things about Visual LISP is that it lets you access applications that utilize the Component Object Model, or COM. This includes AutoCAD itself, Microsoft Office applications, and some others. Accessing Excel isn't that much of a pain, but currently there aren't any really good examples out there for those who haven't done it before, unless you want to dig through posts in the discussion groups.

In this post, I'll briefly show how to add and read values from Excel spreadsheets using Visual LISP.

Before using any vla or vlax functions (which is what we're going to use), you must call (vl-load-com) to load the COM extension. Before we actually look at the actual code, know that COM is object-based. Let's review some of the functions you'll use:

;; creates a new object to use, in our case, "Excel.Application"
(vlax-create-object "object type")

;; retrieves a property of an object
(vlax-get-property object 'property)

;; sets a property to an object
(vlax-put-property object 'property necessary parameters)

;; performs a specified action
(vlax-invoke-method object 'method parameters, if needed)

;; returns the actual value of a variant
;; a variant can be almost anything, thus the name variant
(vlax-variant-value variant)

;; destroys the specified object
(vlax-release-object object)

Right, so let's get right into it. You'll need an Excel spreadsheet to play around with. You can create a new one, but it's almost always easier to make a template for yourself and save it under another name when done.

;; load COM
(vl-load-com)

;; open Excel
(setq excel (vlax-create-object "Excel.Application")

;; get Excel's workbooks
workbooks (vlax-get-property excel 'Workbooks)

;; open an existing spreadsheet
currworkbook (vlax-invoke-method workbooks 'Open "C:\\test.xls")

;; get the active sheet in that spreadsheet
activesheet (vlax-get-property excel 'ActiveSheet)

;; get the cells in that active sheet
cells (vlax-get-property activesheet 'Cells)

row 1
column 1

somedata '("This" "is" "a" "sentence" "with"
"almost" "ten" "words" "in" "it.")
)

;; place the values from somedata into rows 1 through 10, column 1
;; rows and columns start at 1, but list positions start at 0, hence the 1-
(while (< row 11)
(vlax-put-property cells 'Item row column (nth (1- row) somedata))
(setq row (1+ row))
)

;; print the last written value
;; only using prompt with this because we know it is a string
(prompt
(vlax-variant-value
(vlax-get-property
(vlax-variant-value
(vlax-get-property cells 'Item (1- row) column)
)
'Value
)
)
)

;; save the changes
;; returns :vlax-true if successful
(vlax-invoke-method currworkbook 'Save)

;; close all other spreadsheets, if there are any
(vlax-invoke-method workbooks 'Close)

;; quit Excel
(vlax-invoke-method excel 'Quit)

;; destroy all the objects you made, in reverse order
(vlax-release-object cells)
(vlax-release-object activesheet)
(vlax-release-object currworkbook)
(vlax-release-object workbooks)
(vlax-release-object excel)

;; garbage collect in case Excel doesn't close for some reason
(gc)

That's pretty much it. Here's some other handy stuff:

;; create a new spreadsheet
(setq newworkbook (vlax-invoke-method workbooks 'Add))

;; make Excel visible
(vla-put-visible excel :vlax-true)

;; save the current spreadsheet as another file
;; may not work with Excel 2007
(vlax-invoke-method currworkbook 'SaveAs "C:\\some other file.xls"
-4143 nil nil :vlax-false :vlax-false 1 2)

As always, happy lisping.