excel sheet list

[newestuserthishour]

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 ive tried to add in order to get this to happen below. I'm pretty rusty with LISP, so im sure im 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?

[newestuserthishour]

So, I've had no luck updating this so far. The dream would be to have the open excel workbooks populate the first box, then select one, then populate the next box with a list of all sheet names, with the ability to select multiple sheets. then it would have each selected sheet to the Shadi_Xls2Lst function and output the list of info in the same way it currently does. 

 

That's the dream for how this could function, but I would honestly even take being able to type the name of the sheet for it to find if that is easier. 

 

Does anyone have any ideas? I've been stalling out on this for a while now. 

[BIGAL]

Ok some terminology "select multiple sheets" do you mean layouts ? 

 

Then export out say all the title block info onto a row in Excel.

 

Post a dwg and an Excel only real way to see what you want.

 

A hint Lee-mac has a nice select items from a list dcl making a second list of selected. 

[newestuserthishour]

hello! its actually the other way round. I am looking for this to read excel, so it would be looking for open Excel workbooks, look for a selected (or multiple selected) sheets within that Excel workbook, then output the values in each cell to the command line in AutoCAD like this "A1content" "B1Content" "A2Content" and so on for the range of used cells. I'm just not having any luck getting to the list of worksheets in the excel file. 
For examples, it can honestly just be any info within a handful of cells in excel. that will get output into the command line of any blank dwg. It can currently do this, but it only lets you select the workbook that you want and outputs all info in the workbook. I'd like to have it also allow the user to select specific sheets within the workbook so that it isnt outputting ALL info from the workbook combined. 

[BIGAL]

If you look inside this there is a get sheets function, it makes a list of the sheets, you can pass that to a listbox dcl. Just copy that function out. Alan Excel library.lspIt also has the Get & Put cells.

Edited March 7 by BIGAL