Visual Lisp & Excel

How do i connect to Excel using Visual Lisp? Does any one know of a
tutorial shown this? Thanks

 

I just recently spent 6 hours searching this NG and the internet for
information.
I used a couple of "not too easy to follow" examples, and just ended up
trying many options.
This program was written to put a text value in the currently active cell in
a Excel spreadsheet
Remember to release each object!
Good luck....

(defun ACAD2XL (XLTEXT /)
(vl-load-com) ;load extended AutoLISP functions (must be run prior to VL
functions)
;get excel application
(setq XLapp (vlax-get-or-create-object "Excel.Application")
XLrun (vlax-get-property XLapp 'Visible)
)
;check if excel running
(if (= XLrun :vlax-false)
;excel is not running
(progn
(vlax-release-object XLapp)
(setq XLapp nil XLrun nil)
(gc)
(alert "\nERROR - Please Start Excel and select Cell (to place data)
prior to running this command.")
)
;excel is running
(progn
(setq XLwbk (vlax-get-property XLapp 'WorkBooks)
XLash (vlax-get-property XLapp 'ActiveSheet)
XLcel (vlax-get-property XLapp 'ActiveCell)
)
;check if workbook is active
(if (and XLash XLcel)
;excel workbook is active
(progn
(setq XLcol (vlax-get-property XLcel 'Column)
XLrow (vlax-get-property XLcel 'Row)
XLcva (vlax-get-property XLcel 'Text)
XLtxt (vlax-variant-value XLcva)
)
;convert column and row number into Cell text (Example - Column 1,
Row 1 = A1
(if (= (/ XLcol 26) 0)
(setq TC (strcat (chr (+ (rem XLcol 26) 64)) (itoa XLrow)))
(setq TC (strcat (chr (+ (/ XLcol 26) 64)) (chr (+ (rem XLcol
26) 64)) (itoa XLrow)))
)
(setq CELL (strcat TC ":" TC))
;check if cell all ready has data
(if (= XLtxt "")
(setq YN "Y")
(setq YN (strcase (getstring (strcat "\nNOTICE - Excel Cell
contains [" XLtxt "] - Change [n]=No or [Y]=Yes ? "))))
)
(if (member YN (list "" "Y"))
(progn
(setq Cells (vlax-get-property XLash 'Range CELL))
(setq VTXT (vlax-make-variant XLTEXT vlax-vbString))
(vlax-put-property Cells 'Value2 VTXT)
)
)
;release objects
(vlax-release-object Cells)
(vlax-release-object XLcel)
(vlax-release-object XLash)
(vlax-release-object XLwbk)
(vlax-release-object XLapp)
(setq XLapp nil XLrun nil XLwbk nil XLash nil XLcel nil Cells nil)
(gc)
)
;excel is running, but no active workbooks
(progn
(alert "\nERROR - Excel is running, but there are NO workbooks
open.")
(vlax-release-object XLwbk)
(vlax-release-object XLapp)
(setq XLapp nil XLrun nil XLwbk nil)
)
)
)
)
(princ)
)

 

Thank you for the example

 

we have a tool that connects to Excel 3.0 worksheets. if you're interested, i could tweak it to your needs.

--Jeremiah

 

Alan,

Thanks. By chance that came in handy today.

One question: in Excel there's a setting under Tools > Options > Edit tab > Move
selection after Enter. Direction can be Right, Down, etc. Could your function be
modified to take advantage of that? IOW, behave like Enter was pressed after passing
the value. My application of your function is passing numbers from another function
which does area calculations. I wouldn't have to switch to Excel to set the active
cell each time if this can be done.

Or maybe I'm missing something obvious. Or maybe the calling function should be
altered in some way.

Thanks again,
Joe Burke

 

This program was written to allow data from AutoCAD to be put into different
areas of a spreadsheet.It is used to prevent user input errors in Excel from
data calculated in AutoCAD. The program stores the Current Cell - Row
(XLrow) and Column (XLcol). You don't have to set the current cell in Excel
after you run this program. Once you have these values, you can keep track
of them yourself and change them as you need.
Alan

 

Alan,

I understand now.

Thanks
Joe Burke