Leaderboard

I attached the complete lisp. I know it has been said many times before , this site's 1st goal is for learning (no. 2 is free software haha) Shadi.lsp

(if xls-app (Shadi_ReleaseExcel)) ; if there is a header use : (if (and (vl-consp xldata)(vl-consp (setq xl-data (cdr (xl-data))))) (if (vl-consp xldata) (foreach item xldata (initget 0)(if (setq pt (getpoint (strcat "\nSelect point (or space to skip) for item '" (car item) "' , value " (setq txt (itoa (fix (cadr item))))))) (command "text" pt 5 0 txt))) (princ "\nNothing found")) (Shadi_Exit) (princ)

ripped the heart out of one of my routines I made a couple of weeks ago so not sure it's going to work. Did one test only , looked ok , and now its bedtime for me. ; rlx 6 sep 2020 (defun c:shadi ( / actApp actDoc OldErr allOpen xls-app errobj workbooks activesheet activeworkbook allwb 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")) (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))) );end progn (select data from active workbook) (progn (princ "\nExcel is not running . . . initializing\n\n") (Shadi_OpenNewWorkbook)) ) (if xls-app (Shadi_ReleaseExcel)) ; if there is a header use : (if (and (vl-consp xldata)(vl-consp (setq xl-data (cdr (xl-data))))) (if (vl-consp xldata) (foreach item xldata (setq pt (getpoint (strcat "\nSeclect point for item '" (car item) "' , value " (setq txt (itoa (fix (cadr item))))))) (command "text" pt 5 0 txt) ) (princ "\nNothing found") ) (Shadi_Exit) (princ) ) (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 = \"Kies uit lijst\"; 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 ) (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))))) (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 )

There is a get a range of cells function it may not be in getexcel will have a look for it or someone may post. so woul get A1-B3. Get and put to excel requires a bit of experience in lisp so be patient. Look at this (setq addr (strcat (chr (+ 64 c1)) Look for the line you should be able to set your range in the following code as an example. need to convert to a list further down in code. Note its older code so if it has delete it, used to show color in posts screws up code.