adjusting excel columns from autolisp

[Mickey the Gr8]

I am creating an excel worksheet from autolisp to populate with all of my fuse information from autocad electrical 2011. Everything works fine except that I cannot figure out how to change the width of the columns in the excel spreadsheet from autolisp. Any assistance would be greatly appreciated.

This is what I have on the spreadsheet:

 (setq xl (vlax-create-object "excel.application"))
 (setq wb-collection (vlax-get xl "workbooks"))
 (setq workbook (vlax-invoke-method wb-collection "add"))
 (setq sheets (vlax-get workbook "sheets"))
 (setq sheet1 (vlax-get-property sheets "item" 1))
 (setq mywb(vlax-get-property xl "ActiveWorkbook"))
 (setq *excel-cells* (vlax-get sheet1 "cells"))
 (vlax-put xl "visible" 1)

 

;;****************insert header info into spreadsheet**********************
 (setq fuse_cell (strcat "A" (itoa in)))
 (setq cat_cell (strcat "B" (itoa in)))
 (setq mfg_cell (strcat "C" (itoa in)))
 (setq rating1_cell (strcat "D" (itoa in)))
 (setq class_cell (strcat "E" (itoa in)))
 (setq a (vlax-get-property xl 'Range fuse_cell))
 (setq b (vlax-get-property xl 'Range cat_cell))
 (setq c (vlax-get-property xl 'Range mfg_Cell))
 (setq d (vlax-get-property xl 'Range rating1_cell))
 (setq e (vlax-get-property xl 'Range class_cell))
 (vlax-put-property a 'Value2 "FUSE")
 (vlax-put-property b 'Value2 "PART NUMBER")
 (vlax-put-property c 'Value2 "MANUFACTURER")
 (vlax-put-property d 'Value2 "RATING")
 (vlax-put-property E 'Value2 "CLASS")
 (setq in 2); sets row in spreadsheet
 
 ;*****************************populate excel sheet****************************************
 (repeat (length fuse_name)
  (setq fuse_cell (strcat "A" (itoa in)))
  (setq cat_cell (strcat "B" (itoa in)))
  (setq mfg_cell (strcat "C" (itoa in)))
  (setq rating1_cell (strcat "D" (itoa in)))
  (setq class_cell (strcat "E" (itoa in)))
  (setq a (vlax-get-property xl 'Range fuse_cell))
  (setq b (vlax-get-property xl 'Range cat_cell))
  (setq c (vlax-get-property xl 'Range mfg_Cell))
  (setq d (vlax-get-property xl 'Range rating1_cell))
  (setq e (vlax-get-property xl 'Range class_cell))
  (vlax-put-property a 'Value2 (nth in_lst fuse_name))
  (vlax-put-property b 'Value2 (nth in_lst fuse_Cat))
  (vlax-put-property c 'Value2 (nth in_lst fuse_mfg))
  (vlax-put-property d 'Value2 (nth in_lst fuse_rating1))
  (vlax-put-property e 'Value2 (nth in_lst fuse_class))
  (setq in_lst (1+ in_lst))(princ)
  (setq in (1+ in))(princ)
 )

[Grrr]

Heres a simple example to change the width of the third column:

(setq xlapp (vlax-get-or-create-object "Excel.Application"))
(vlax-put-property xlapp 'Visible :vlax-true)
(setq xlwbks (vlax-get-property xlapp 'WorkBooks))
(setq xlwbk (vlax-invoke-method xlwbks 'Add))
(setq xlshts (vlax-get-property xlwbk 'Sheets))
(setq xlsht (vlax-get-property xlshts 'Item 1))
(setq xlcols (vlax-get-property xlsht 'Columns))
(setq ThirdColumn (vlax-get-property (vlax-variant-value (vlax-get-property xlcols 'Item 3)) 'EntireColumn))
(vlax-put-property ThirdColumn 'ColumnWidth 16)

FWIW To change the width of all columns in that worksheet:

(vlax-put-property xlcols 'ColumnWidth 16)

But perhaps I assume you'd want to invoke the AutoFit method:

(vlax-invoke-method xlcols 'AutoFit)

 

---

And to release the excel objects, and reset to nil the assigned symbols from the above manual test:

(foreach x '(xlapp xlwbks xlwbk xlshts xlsht xlcols ThirdColumn)
  (
    (lambda (x)
      (and (eq 'VLA-OBJECT (type x)) (vl-catch-all-apply (function vlax-release-object) (list x)))
    )
    (eval x)
  )
  (set x nil)
)

 

Edited December 3, 2018 by Grrr

[Mickey the Gr8]

Thank you very much.

There is not a lot of info out there on manipulating excel through autolisp.

Your help is appreciated.

Thanks a lot.

[Lee Mac]

11 minutes ago, Mickey the Gr8 said:

There is not a lot of info out there on manipulating excel through autolisp.

 

If you're interested in manipulating MS Excel using Visual LISP, this should be your bible.

 

The reference is obviously VBA, but the translation from VBA to Visual LISP is relatively straightforward.

 

Lee

[Grrr]

No problem,

The concept is the same as in any other language - you investigate the COM hierarchy of the excel application to become familiar.

And then you just access the available objects/properties/methods required for your work.

Just google for excel+lisp to get some examples (Lee Mac's are the best ones)

 

---

One thing though - with LISP you cannot assign events, while they are contained in the VB/.NET languages.