hello all,
I am looking to update this code in order for it to present me with a selection for tabs (or sheets) in the excel workbook, let me choose which sheets I want it to read, then give the output looking at only those sheets.
I think I am kind of close? I will notate what I've tried to add in order to get this to happen below. I'm pretty rusty with LISP, so I'm sure I'm doing something wrong here.
;;-------------------------------------------------------------;;
;; ReadExcel ;;
;;; Written by "Shadi" in 2020, lifted from "CADTutor" ;
;;; forum. ;
;;; Forum Link: ;
;;; https://www.cadtutor.net/forum/topic/71141-lisp-to-look-up-in-excel-and-insert-text-in-cad/
;;; ;
;;-------------------------------------------------------------;;
; rlx 6 sep 2020
(defun c:ReadExcel ( / actApp actDoc OldErr allOpen xls-app errobj shtlst selshts workbooks activesheet sheets activeworkbook allwb allsht xldata pt txt)
(Shadi_Init)
(if (setq xls-app (vlax-get-object "Excel.Application"))
(progn
(princ "\nExcel is running\n\n")
;;; list all open workbooks
(setq errobj (vl-catch-all-apply (function (lambda () (setq workbooks (vlax-get-property xls-app "Workbooks"))))))
(if (vl-catch-all-error-p errobj) (princ "\nUnable to get 'workbooks property from excel"))
;;; get active sheet
(setq errobj (vl-catch-all-apply (function (lambda () (setq activesheet (vlax-get-property xls-app "activesheet"))))))
(if (vl-catch-all-error-p errobj)(princ "\nUnable to get 'activesheet property from excel"))
; ;;; list all sheets
; (setq errobj (vl-catch-all-apply (function (lambda () (setq sheets (vlax-get-property xls-app "Sheets"))))))
; (if (vl-catch-all-error-p errobj) (princ "\nUnable to get 'sheets property from excel"))
(if workbooks
(vlax-for wb workbooks (vl-catch-all-apply (function (lambda ()(setq allwb (append allwb (list (vla-get-fullname wb)))))))))
(princ "\nAll open workbooks :\n")
(if (and allwb (setq wb (Shadi_cfl allwb)))
(Shadi_OpenActiveWorkbook wb) (progn (princ "\nNo open workbooks found - switching to manual selection")(Shadi_OpenNewWorkbook)))
;;THIS IS THE FARTHEST IVE GOTTEN. IT FAILS HERE
(setq shtlst (vl-catch-all-apply 'vlax-get-property (list (vlax-get-property wb "Sheets"))))
(if (vl-catch-all-error-p errobj)(princ "\nUnable to get 'sheets property from excel"))
(setq selshts (Shadi_shtsel shtlst))
);end progn (select data from active workbook)
(progn (princ "\nExcel is not running . . . initializing\n\n") (Shadi_OpenNewWorkbook))
)
(if xls-app (Shadi_ReleaseExcel))
(Shadi_Exit)
(if (vl-consp xldata)
xldata
nil
)
)
(defun Shadi_Err ($s) (princ $s)(Shadi_Exit)(setq *error* OldErr)(princ))
(defun Shadi_Exit () (Shadi_ReleaseAll)(gc))
(defun Shadi_Init () (vl-load-com) (setq OldErr *error* *error* Shadi_Err)
(setq actApp (vlax-get-acad-object) actDoc (vla-get-activedocument actApp))
(vlax-for doc (vla-get-documents actApp)(setq allOpen (cons (cons (strcase (vla-get-fullname doc)) doc) allOpen))))
(defun Shadi_ReleaseAll ()
(vl-catch-all-apply 'vlax-release-object (list actdoc))(vl-catch-all-apply 'vlax-release-object (list actapp)))
; choose from list
(defun Shadi_cfl ( lst / dcl-name fp dcl drv inp)
(if (not (findfile (setq dcl-name (strcat (getvar "MYDOCUMENTSPREFIX") "\\Shadi_cfl.dcl"))))
(progn (setq fp (open dcl-name "w")) (write-line (strcat "ask : dialog { label = \"Please choose an active Workbook:\"; spacer_1;"
" : list_box { key = \"lbox\"; width = 100;} spacer_1; ok_cancel;}") fp) (if fp (close fp))(gc)))
(if (and lst (setq dcl (load_dialog dcl-name))(new_dialog "ask" dcl))
(progn (vl-file-delete dcl-name) (start_list "lbox")(mapcar 'add_list lst)(end_list)
(action_tile "lbox" "(setq inp (nth (atoi $value) lst))") (action_tile "ok" "(done_dialog 1)")
(action_tile "cancel" "(done_dialog 0)") (setq drv (start_dialog))
(if (and (= drv 1)(not inp)) (setq inp (nth 0 lst)))) (setq inp '()))
inp
)
;;;THIS BELOW IS FROM LEE MAC TO CREATE A DCL WHERE YOU CAN SELECT MULTIPLE ITEMS IN THE LIST
;; choose sheets from list
(defun Shadi_shtsel ( msg lst bit / dch des tmp rtn )
(cond
((not (and (setq tmp (vl-filename-mktemp nil nil ".dcl"))
(setq des (open tmp "w"))
(write-line
(strcat "listbox:dialog{label=\"Please choose sheets:\";spacer;:list_box{key=\"list\";multiple_select="
(if (= 1 (logand 1 bit)) "true" "false") ";width=50;height=15;}spacer;ok_cancel;}"
)
des
)
(not (close des))
(< 0 (setq dch (load_dialog tmp)))
(new_dialog "listbox" dch)
)
)
(prompt "\nError Loading List Box Dialog.")
)
( t
(start_list "list")
(foreach itm lst (add_list itm))
(end_list)
(setq rtn (set_tile "list" "0"))
(action_tile "list" "(setq rtn $value)")
(setq rtn
(if (= 1 (start_dialog))
(if (= 2 (logand 2 bit))
(read (strcat "(" rtn ")"))
(mapcar '(lambda ( x ) (nth x lst)) (read (strcat "(" rtn ")")))
)
)
)
)
)
(if (< 0 dch)
(unload_dialog dch)
)
(if (and tmp (setq tmp (findfile tmp)))
(vl-file-delete tmp)
)
rtn
)
(defun Shadi_OpenActiveWorkbook ( fn / wb activeworkbook activesheet)
(if (and xls-app fn (findfile fn) workbooks) (progn (setq activeworkbook (vlax-invoke-method workbooks 'open fn))
(setq activesheet (vlax-get-property xls-app 'activesheet)) (setq xldata (Shadi_Xls2Lst activesheet)))))
;TRIED HERE TO GET IT TO READ ONLY THE SELECTED SHEETS
; (defun Shadi_OpenActiveWorkbooksheet ( fn / sht sheets activeworkbook activesheet)
; (if (and xls-app fn (findfile fn) workbooks) (progn (setq activeworkbook (vlax-invoke-method workbooks 'open fn))
; (setq sheets (vlax-get-property xls-app 'sheets)) (setq xldata (Shadi_Xls2Lstsheets sheets)))))
(defun Shadi_OpenNewWorkbook ( / fn err path)
(if (and Shadi-LastXlsFile (findfile Shadi-LastXlsFile))(setq path (vl-filename-directory Shadi-LastXlsFile))(setq path ""))
(if (and (setq fn (getfiled "Select Excel File (*.xlsx is default, change type if needed)" path "xlsx;xls;xlsm" 16))
(setq xls-app (vlax-get-or-create-object "excel.application")))
(progn (setq Shadi-LastXlsFile fn err (vl-catch-all-apply (function (lambda ()(setq workbooks (vlax-get-property xls-app 'workbooks)
activeworkbook (vlax-invoke-method workbooks 'open fn) activesheet (vlax-get-property xls-app 'activesheet))))))
(if (vl-catch-all-error-p err) (prompt (strcat "\nError: " (vl-catch-all-error-message err)))
(progn (vlax-invoke-method activesheet "Activate")(graphscr)(setq xldata (Shadi_Xls2Lst activesheet))))))
(vl-catch-all-apply (function (lambda ()(vlax-invoke-method activeworkbook 'close :vlax-false))))
(vl-catch-all-apply (function (lambda ()(vlax-invoke-method xls-app 'quit))))(Shadi_CloseExcel)
)
(defun Shadi_CloseExcel () (if xls-app (progn (vl-catch-all-apply 'vlax-invoke-method (list activeworkbook "Close" :vlax-False))
(vl-catch-all-apply 'vla-quit (list xls-app)) (mapcar '(lambda(x)(vl-catch-all-apply 'vlax-release-object (list x)))
(list activesheet activeworkbook xls-app)) (setq xls-app nil activeworkbook nil activesheet nil) (gc))))
(defun Shadi_ReleaseExcel () (mapcar '(lambda(x) (if x (vl-catch-all-apply 'vlax-release-object (list x))))
(list activesheet activeworkbook xls-app)) (setq xls-app nil activeworkbook nil activesheet nil) (gc))
(defun Shadi_Xls2Lst
( %s / excel-range excel-used-range excel-max-row excel-max-column excel-max-range excel-variant excel-value excel-to-list lst)
(defun _cl (i / n) (if (< i 27)(chr (+ 64 i))(if (= 0 (setq n (rem i 26)))
(strcat (_cl (1- (/ i 26))) "Z")(strcat (_cl (/ i 26))(chr (+ 64 n))))))
(setq excel-used-range (vlax-get-property %s 'UsedRange))
(setq excel-max-row (vlax-get-property (vlax-get-property excel-used-range "Rows") "Count"))
(setq excel-max-column (vlax-get-property (vlax-get-property excel-used-range "Columns") "Count"))
(setq excel-max-range (strcat "A1:" (_cl excel-max-column) (itoa excel-max-row)))
(setq excel-range (vlax-get-property %s 'Range excel-max-range))
(setq excel-variant (vlax-get-property excel-range 'Value))
(setq excel-value (vlax-variant-value excel-variant))
(setq excel-to-list (vlax-safearray->list excel-value))
(setq lst (mapcar '(lambda (x) (subst "" nil (mapcar '(lambda (y) (vlax-variant-value y)) x ))) excel-to-list))
(princ)
lst
)
;;-------------------------------------------------------------;;
;; End of ReadExcel by Shadi ;;
;;-------------------------------------------------------------;;
The output currently is fine how it is. problem is that it will output only the active sheet of info when I need it to allow me to pick several sheets and push them all out into the current output format.
any ideas?
