Search the Community

Hello to everyone. I have a file that can draw the longitudinal path of a water line, but it lacks some technical details, such as drawing the boundaries of the table beneath the chart and signing the depth calculated by the lisp as it is, rather than minus a distance estimated by the pipe's radius. Please seek assistance from people with relevant experience. Attachment: Assign Excel and Lisp next to a CAD file that almost has a work model with some notes. WATER PROFILE SPREADSHEET.xlsx TEST PROFILE NOT COMPLETING DRAWING BY LISP AND XLS.dwg WaterProfile.LSP

I created a spreadsheet of about 40 rows to be used to create different parts. I then created a part "we'll call it Board_A". I linked to the spreadsheet and used 3 of the fields for my dimensions. Lets say I named the length (which was the extrusion number) as "Len_1". I saved it and all is well!! I then created other parts and used the spreadsheet for the dims. I then went back and opened "Board_A" and saved a copy as "Board_B". I closed Board_A and opened Board_B. I then rt clicked on "Extrusion" so I could change the length from "Len_1" to "Len_1_A" which is another row (field) on that same spreadsheet which is still linked. When I tried I received a message that said I couldn't do that (I forgot the actual message!) I clicked the Parameter icon and the only fields showing on the popup spreadsheet box were the ones I had previously used for those parts that I made from scratch. I thought I figured this out once but if I did I forgot. I deleted the spreadsheet link and then re-added it but nothing I have tried has worked. Is there a way to get these fields to be usable with a Copy of an existing part or do I just have to start from scratch and make the similar part? I hope this makes sense!! Thanks JR_Ohio

Hi, I'm attempting to modify Fixo's code at bottom to read data from an excel spreadsheet that's formatted a bit differently than assumed by the code. The code here assumes X, Y, and Z scale each live in a separate exel column/cell. However, I have existing spreadsheets where these dimensions were entered as a text string in a single cell. For example, the cell might read 6x5.5x24. The only constant is the "x" between the dimensions. The text numbers might be one, two or even three characters and may or may not include decimals. My thought is to modify these lines below to somehow check the same string for the X's and truncate according its position to extract the desired X, Y or Z value. But, I don't know enough yet to even know if this is possible. xscale (atof (vl-princ-to-string (nth 5 item))) yscale (atof (vl-princ-to-string (nth 6 item))) zscale (atof (vl-princ-to-string (nth 7 item))) Failing this, I'll make a second version of the existing spreadsheets and have users copy data into new columns formatted to work with the existing code. Many thanks! ;;;---------------------------------------------------------------------------; ;;; ;;; getexel.lsp ;;; ;;; based on code posted by "fixo" on [url]http://forums.augi.com/showthread.php?t=112689&highlight=blocks+excel[/url] ;;; This lisp is used to insert a block multiple times based on coordinates and scale factors listed in an excel spreadsheet ;;; Data in spreadsheet must follow this format: ;;; first row is headers/labels for data ;;; cola-empty, colb-x coord, colc-y coord, cold-z coord, colf-width/x scale, colg-depth/y scale, colh-height/z scale, ;;; coli-layer destination name, colj-equip tag number. ;;; ;;;---------------------------------------------------------------------------; ;;; ;;; DESCRIPTION ;;; ;;; Configure AutoCAD ;;; ;;;****************************************************** ;;; Section 1 ;;;****************************************************** ;;; ;;; --- Load Visual Lisp Support ;;; (VL-LOAD-COM) ;;;****************************************************** ;;; Section 2 ;;;****************************************************** ;;; read used range from Excel ;;; (defun getexcelinfo (Wbk ShtNum / Sht UsdRange ExcData) (setq Sht (vl-catch-all-apply 'vlax-get-property (list (vlax-get-property Wbk "Sheets") "Item" ShtNum ) ) ) (setq UsdRange (vlax-get-property Sht "UsedRange") ExcData (vlax-safearray->list (vlax-variant-value (vlax-get-property UsdRange "Value") ) ) ) (setq ExcData (mapcar (function (lambda (x) (mapcar 'vlax-variant-value x))) ExcData ) ) ExcData ) ;;;****************************************************** ;;; Section 3 ;;;****************************************************** (defun C:getexcel (/ *error* acsp adoc aexc att1 att2 att3 att4 attribs block_data blockname block_obj dwgpath layer rot x xlbook xlbooks xlpath xscale y yscale z zscale) (defun *error* (msg) (vl-bt) (if (vl-position msg '("console break" "Function cancelled" "quit / exit abort" ) ) (princ "Error!") (princ msg)) (vla-endundomark adoc) (vl-exit-with-value "Program bombed, sorry") ) (if (setq xlpath (getfiled "* Select Excel File To Read Data*" "" "xlsx" 4) ) (progn (setq aexc (vlax-get-or-create-object "Excel.Application") xlbooks (vlax-get-property aexc "Workbooks")) (setq xlbook (vlax-invoke-method xlbooks "Open" xlpath)) (setq block_data (getexcelinfo xlbook 1));<-- 1 is excel sheet number, change to suit (setq block_data (vl-remove-if-not (function (lambda (x) (car x))) block_data)) (setq block_data (cdr block_data)) (vl-catch-all-apply (function (lambda () (vlax-invoke-method xlbook "Close" :vlax-false))) ) (mapcar (function (lambda (x) (vl-catch-all-apply (function (lambda () (progn (if x (vlax-release-object x))))))) ) (list xlbook xlbooks aexc) ) (setq aexc nil) (gc) (gc) (setq dwgpath (getfiled "* Select Drawing With Block To Insert *" "" "dwg" 4) ) (setq blockname (vl-filename-base dwgpath)) (setq adoc (vla-get-activedocument (vlax-get-acad-object))) (setq acsp (vla-get-block (vla-get-activelayout adoc))) (vla-startundomark adoc) (vl-catch-all-apply (function (lambda () (progn (setq block_obj (vlax-invoke-method acsp 'InsertBlock (vlax-3d-point '(0 0 0)) dwgpath 1 1 1 0 ) ) (vla-delete block_obj) (vlax-release-object block_obj)))) ) ;; I know nothing what is data format in your Excel ;; thus I have added extrafluous convertion of them (foreach item block_data (setq x (atof (vl-princ-to-string (nth 1 item))) y (atof (vl-princ-to-string (nth 2 item))) z (atof (vl-princ-to-string (nth 3 item))) rot (atof (vl-princ-to-string (nth 4 item))) xscale (atof (vl-princ-to-string (nth 5 item))) yscale (atof (vl-princ-to-string (nth 6 item))) zscale (atof (vl-princ-to-string (nth 7 item))) layer (vl-princ-to-string (nth 8 item)) att1 (vl-princ-to-string (nth 9 item)) att2 (vl-princ-to-string (nth 5 item)) att3 (vl-princ-to-string (nth 6 item)) att4 (vl-princ-to-string (nth 7 item)) ) (setq block_obj (vlax-invoke-method acsp 'InsertBlock (vlax-3d-point (list x y z)) dwgpath xscale yscale zscale rot) ) (if (tblsearch "layer" layer) (vla-put-layer block_obj layer) (princ (strcat "\nLayer " "\"" layer "\"" " does not exist")) ) (setq attribs (vlax-invoke block_obj 'GetAttributes)) (foreach att attribs (cond ((eq "EQ-TAG" (vla-get-tagstring att));<--"EQ-TAG" is the first tag (vla-put-textstring att att1)) ((eq "EQ-WIDTH" (vla-get-tagstring att));<--"EQ-WIDTH" is the second tag (vla-put-textstring att att2)) ((eq "EQ-DEPTH" (vla-get-tagstring att));<--"EQ-DEPTH" is the THIRD tag (vla-put-textstring att att3)) ((eq "EQ-HGHT" (vla-get-tagstring att));<--"EQ-HGHT" is the FOURTH tag (vla-put-textstring att att4)) ;<-- add other tags here by the same way as above (T nil) ) ) ) ) ) (*error* nil) (prin1) ) (prompt "\n\t\t>>>\tType getexcel to execute\t<<<") (prin1) (vl-load-com)~'J'~