Writing to Excel with LISP

[Fraser Atkinson]

Hello all,

I am trying to writing some data to an Excel (2002) spreadsheet using LISP
(ACAD 2004). I know, I know, its much easier with VBA, but I unfortunately
don't know VBA. So it's got to be LISP.
I think I'm doing everything right up to the point where I send a variant
array to an Excel range. ACAD gives me the error "error: Automation Error.
Description was not provided."

I'm missing something here. Can anybody help? either with the code below
or by pointing me to a good resource that I can use to figure this out?

Help, as always, is GREATLY appreciated.

(vl-load-com)
(if (not excm-open)
(vlax-import-type-library
:tlb-filename "C:/Program Files/Microsoft Office/Office10/Excel.exe"
:methods-prefix "msxl-"
:properties-prefix "msxl-"
:constants-prefix "msxl-")
)

(setq ExcelApp (vlax-get-or-create-object "Excel.Application")
ExcelWBC (vlax-get-property ExcelApp 'WorkBooks)
NewWB (vlax-invoke-method ExcelWBC 'add)
);setq
(vlax-put-property ExcelApp 'visible 1)

(setq NewWS (vlax-get-property NewWB 'worksheets)
NewWS# (vlax-get-property NewWS 'item wkst#)
Start "A"
End (if (< (/ (length (car lst)) 26) 1)
(chr (+ 64 (length (car lst))))
(strcat (chr (+ 64 (/ (length (car lst)) 26)))
(chr (+ 64 (fix (* 26 (- (/ (length (car lst)) 26.0) (/
(length (car lst)) 26))))))))
Range (strcat Start "1:" End "1")
sfarray (vlax-make-safearray vlax-vbVariant (cons 1 (length (car
lst))))
);setq

(setq lst1 (mapcar '(lambda (x) (mapcar 'vlax-make-variant x)) lst))

(setq counter 1)
(foreach n lst1
(vlax-safearray-fill sfarray n)
(setq vsfarray (vlax-make-variant sfarray)
oRng (msxl-get-range NewWS# Range)
);setq
(msxl-put-value oRng sfarray) ;;;;;;;;; This is where my problem is.
(setq counter (1+ counter))
(setq Range (strcat "A" (itoa counter) ":" (substr range 4 1) (itoa
counter)))
(vlax-release-object oRng)
);foreach

;; Release all object and close Excel.
(vlax-release-object oRng)
(vlax-release-object NewWS#)
(vlax-release-object NewWS)
(vlax-release-object NewWB)
(msxl-saveas ExcelWBC fname nil nil nil nil nil nil)

Fraser Atkinson
Cleat...@magma.ca

[Jon Fleming]

I have no idea what is the problem with your code.

FWIW, here's a little routine I put together once to put data into
Excel through ActiveX Data Objects. It does not require that Excel be
open; it plops the values directly into the file. It uses my ADOLISP
Library, free from the download page at http://www.fleming-group.com.
It's hard-coded to insert string values, but you could change it to
non-strings by remonving the single-quotes from the line:

"] VALUES ('" Value "')"

or make it more sophisticated by testing the argument type and
including the single quotes only for strings.

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

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