Visual Lisp & Excel

Discussion:

Visual Lisp & Excel

(too old to reply)

ChrisW

2004-04-08 19:20:15 UTC

Permalink

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

Alan Henderson

2004-04-08 21:37:20 UTC

Permalink

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)
)

ChrisW

2004-04-08 21:58:16 UTC

Permalink

Thank you for the example

Post by Alan Henderson
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)
)

Joe Burke

2004-04-09 12:35:29 UTC

Permalink

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

Alan Henderson

2004-04-09 13:57:05 UTC

Permalink

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

Joe Burke

2004-04-10 06:47:52 UTC

Permalink

Alan,

I understand now.

Thanks
Joe Burke

Post by Alan Henderson
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

liftedaxis

2004-04-09 04:38:43 UTC

Permalink

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

--Jeremiah

Jon Fleming

2004-04-09 12:24:34 UTC

Permalink

There are also several third-party tools that connect AutoCAD and
Excel. I can supply a few names if you are interested.

In addition to any replies you might receive or already received, you
may find more information or responses by posting future connectivity
related questions in the following discussion group:

Web browser: <http://discussion.autodesk.com/forum.jspa?forumID=16>
Newsreader:
<news://discussion.autodesk.com/autodesk.autocad.connectivity>

--
jrf
Autodesk Discussion Group Facilitator
Please do not email questions unless you wish to hire my services

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

Jon Fleming

2004-04-09 12:24:33 UTC

Permalink

You can also connect to an Excel worksheet without having Excel
running, or even installed, using ADO. Here's a simple routine that
uses my free ADOLISP library from http://www.fleming-group.com:

(defun PutIntoSpreadsheet (FileName SheetName CellName Value /
Connection ReturnValue
)
(if (setq Connection
(ADOLISP_ConnectToDB
(strcat
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
FileName
";Extended Properties=\"Excel 8.0;HDR=No;\";Persist
Security Info=False"
)
""
""
)
)
(progn (if (not
(setq ReturnValue
(ADOLISP_DoSQL
Connection
(strcat "INSERT INTO [" SheetName "$"
CellName ":" CellName
"] VALUES ('" Value "')"
)
)
)
)
(ADOLISP_ErrorPrinter)
)
(ADOLISP_DisconnectFromDB Connection)
)
)
ReturnValue
)

(defun Test ()
(if (PutIntoSpreadsheet
"D:\\WriteTest.xls"
"Sheet1"
"C3"
"Hi There!"
)
(prompt "OK!")
)
(prin1)
)

Values can be extracted similarly (using "SELECT * FROM [" SheetName
"$" CellName ":" CellName "]").

--
jrf
Autodesk Discussion Group Facilitator
Please do not email questions unless you wish to hire my services

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