topic Re: Help in Customizing Lisp Code in Visual LISP, AutoLISP and General Customization Forum

Help in Customizing Lisp Code

clerigoaaron — Tue, 01 Oct 2024 12:40:53 GMT

I have a Lisp code from a community member that compiles different pipe dxfs into 1 single file. It also inserts the pipes drawing number and pipe number as it is compiling. The data is extracted from an excel spread sheet and is cross referenced with the dxfs file name (also the drawing number). This Lisp only works if the data in the excel starts at Column A row 2. How can I change the code for it work even if the data starts at column B row 26? Thank you. Please see the code and excel below.

(Defun c:MakeThisHAppen ( /  _Entnext ExtractFromXls el moveAll fileName XL-App Workbooks exData f textObj moveAll)
(defun _Entnext ( e )
    (if (setq e (entnext e)) (cons e (_Entnext e)))
  )  
(defun ExtractFromXls (st app col / ncol val dataCollection)
      (repeat col
	(setq ncol (cons col ncol))
	(setq col (1- col))
      )
       (while
	 (vl-every 'eval
		   (setq val 
			  (mapcar '(lambda (i / cv)
				     (if (Setq cv (vlax-get-property (vlax-get (vlax-get app "ActiveSheet") 'Cells) 'Item st i))
                  			(vlax-variant-value
					  (vlax-get-property
					    (vlax-variant-value cv)
					    'Value
					  )
					)
				       )
				     ) ncol)
			  
		   	)
		   )
	 (setq dataCollection (cons (mapcar 'vl-princ-to-string val) dataCollection))
	 (setq st (1+ st))
	 )
      dataCollection
       )
			     
(if (and
      		(setq  Directory (acet-ui-pickdir
				 "Select Project Folder" (if Directory Directory (getvar 'dwgprefix))))
      		(setq moveAll (ssadd)
		       dxfFiles (vl-directory-files directory "*.dxf" 1))
		(setq fileName (getfiled "Excel Spreadsheet File"
                                   (if fileName fileName (getvar 'dwgprefix))"XLSX;XLS" 16 ))
        	(setq XL-App    (vlax-get-or-create-object "Excel.Application"))      		
      		(Setq Workbooks (vlax-invoke-method (vlax-get-property XL-App 'WorkBooks)
                                    'Open  fileName ))
      		(setq exData (extractfromxls 1 XL-App 2))
      		
      )
  (progn
	(foreach fname dxfFiles	  
	  (if (Setq f (Assoc (strcase (vl-filename-base  fname)) exData))
		(progn
		  (setq el (entlast))
		  (command "_DXFIN" (strcat directory "\\" fname) '(0.0 0.0 0.0))
		  (if (null el)(and (setq el (entnext)) (ssadd el moveAll)))
		  (mapcar '(lambda ( x ) (ssadd x moveAll)) (_Entnext el))		  
		  (foreach strdata (list
				     '((0.0 0.0 0.0) 12 "Proj-123")
				     (list '(0.0 -30.00 0.0) 12
				       (strcat "Dwg no: " (vl-filename-base fname)
				       )
				     )
				     (list '(0.0 -45.0 0.0) 12 (strcat "Pipe no: " (Cadr f))
				     )
				   )
		  (setq textObj (entmakex (list (cons 0 "TEXT")
					    (cons 10 (car strdata))
					    (cons 40 (cadr strdata))
					    (cons 1 (caddr strdata))
				      )
			    )
		  )
		    
		    (ssadd textObj moveAll))		    
		    (command "_move" moveAll "" "_non" "0,0,0" "0,500,0")
		    )
	    (princ (Strcat "\n" (vl-filename-base  fname) " Not found on source list"))
	    )
		)
		
  		(vlax-invoke-method Workbooks 'close  fileName )
	    	(vlax-release-object XL-App)
		(vlax-release-object Workbooks)
    )
	    		
  (Vl-some '(lambda (l)
	      (if (null (eval (Car l)))(print (cadr l))))
	   		'((Directory "No folder selected/Cancelled by user")
			  (dxfFiles "No DXF files found on selected folder")
			  (fileName "No xls:x;sx file found on selected folder / Cancelled by user")
			  (XL-App "Excel application not found")
			  )
		)	   
  )
  (princ)
  )

Re: Help in Customizing Lisp Code

Simon_Weel — Tue, 01 Oct 2024 14:23:08 GMT

This is the line where data is extracted from the Excel file:

(setq exData (extractfromxls 1 XL-App 2))

As you can see, the function extractfromxls is called with three arguments: 1 XL-App 2. In the function itself represented by st app col. I guess st is the row number and col is the (starting?) column number.

Give it a try and change those numbers and see what happens?

Re: Help in Customizing Lisp Code

clerigoaaron — Tue, 01 Oct 2024 23:12:55 GMT

I changed it to 26 XL-App 2 and it works. Started extracting data at column A row 26. But following your instructions shouldnt this be column B row 26? I cant think of how to change the column part

Re: Help in Customizing Lisp Code

Sea-Haven — Wed, 02 Oct 2024 05:03:52 GMT

There was a contibutor no longer with us FIXO who did lots of stuff with Excel i have a few of his routines, one is to select a range and return that range as a List.

; Thanks to FIXO for get range (defun getrangexl2 ( / lst UR CR RADD ) (vl-catch-all-error-p (setq Rng (vl-catch-all-apply (function (lambda () (vlax-variant-value (vlax-invoke-method (vlax-get-property myxl 'Application) 'Inputbox "Select a Range: " "Range Selection " nil nil nil nil nil 8)))))) ) (setq xrng (vlax-get-property rng "address")) (setq xxrng xrng) (repeat 4 (setq xxrng(vl-string-subst "" "$" xxrng))) (setq xxxrng (_csv->lst58 xxrng)) (setq rngst (columnrow (nth 0 xxxrng)) rngend (columnrow (nth 1 xxxrng))) (setq *ExcelData@ nil ) (setq Row# (nth 1 rngst)) (repeat (+ (- (nth 1 rngend)(nth 1 rngst) ) 1) (setq Data@ nil) (setq Column# (nth 0 rngst)) (repeat (+ (- (nth 0 rngend)(nth 0 rngst) ) 1) (setq Range$ (strcat (Number2Alpha Column#)(itoa Row#))) (setq ExcelRange (vlax-get-property myxl "Range" range$)) (setq ExcelVariant (vlax-get-property ExcelRange 'Value)) (setq ExcelValue (vlax-variant-value ExcelVariant)) (setq Data@ (append Data@ (list ExcelValue))) (setq Column# (1+ Column#)) ) (setq *ExcelData@ (append *ExcelData@ (list Data@))) (setq Row# (1+ Row#)) ) (princ) )

The answer is in the variable *ExcelData@

You need to change the myxl to your application name variable.

(vlax-get-property myxl 'Application)

Re: Help in Customizing Lisp Code

pbejse — Wed, 02 Oct 2024 06:46:47 GMT

@clerigoaaron wrote:

How can I change the code for it work even if the data starts at column B row 26? Thank you. Please see the code and excel below.
(Defun c:MakeThisHAppen ( /  _Entnext ExtractFromXls el moveAll fileName XL-App Workbooks exData f textObj moveAll)
....

This code looks familiar.

This would be an easy tweak.

Re: Help in Customizing Lisp Code

clerigoaaron — Wed, 02 Oct 2024 09:10:03 GMT

Thats because is your code @pbejse I've been trying to study it for a while now but its so hard because I am starting from the basics

Re: Help in Customizing Lisp Code

ec-cad — Wed, 02 Oct 2024 17:56:00 GMT

In the code, I think 'st is short for 'St arting Row', and col is Column

Just guessing, Columns probably A=2, B=3 from what you are getting.

So try 26 XL-App 3

ECCAD

Re: Help in Customizing Lisp Code

clerigoaaron — Thu, 03 Oct 2024 08:06:14 GMT

@ec-cad i tried that at it did not work. No error message show just nothing happened after running the lisp

Re: Help in Customizing Lisp Code

ec-cad — Thu, 03 Oct 2024 17:30:02 GMT

OK,

I'll look into it, be back later.

ECCAD

Re: Help in Customizing Lisp Code

ec-cad — Thu, 03 Oct 2024 18:29:59 GMT

OK,

I think I have a fix. Seems the 2 on the end is # of Columns, not position of Column.

Try these changes:
Change this:
(defun ExtractFromXls (st app col / ncol val dataCollection)
To be:
(defun ExtractFromXls (st app col n / ncol val dataCollection)
.. added a n as 4th variable
And:
Change this:
(if (Setq cv (vlax-get-property (vlax-get (vlax-get app "ActiveSheet") 'Cells) 'Item st i))
To be:
(if (Setq cv (vlax-get-property (vlax-get (vlax-get app "ActiveSheet") 'Cells) 'Item st (+ i n)))

To get Row 26 and Column B, do:
(setq exData (extractfromxls 26 XL-App 2 1))

Seems like the first 2 is the # of Columns, not the 'index' to Column B
Added the n to the Function, to allow spefifying 'which' Column
where: n= 0 = A, 1 = B, 2 = C

Preliminary test shows:
Test 1 with Col 1
EXDATA=((04 15) (03 14) (02 13.0) (21.0 12R) (2BR 12L))

And, note: you need the Excel Cells formatted as 'text'

ECCAD

Re: Help in Customizing Lisp Code

clerigoaaron — Fri, 04 Oct 2024 15:46:26 GMT

@ec-cad @It worked as intended, the data extraction started at column B row 26. But for some reason it only worked until pipe 10. Pipes 11 onwards showed erro

Re: Help in Customizing Lisp Code

ec-cad — Fri, 04 Oct 2024 17:36:16 GMT

What was the error ?

ECCAD

Re: Help in Customizing Lisp Code

ec-cad — Fri, 04 Oct 2024 18:21:58 GMT

If I recreated the xlsx file correctly,
And using: (setq exData (extractfromxls 26 XL-App 2 1)); Column B

This is what's in exData list:
((28.0 25.0) (27.0 24.0) (26.0 23.0) (25.0 22.0) (24.0 21.0) (13.0 13.0) (11 11) (10R 10) (10L 10) (09R 9) (09L 9) (08R 8-1,8-2,8-3,8-4) (08L 8-1,8-2,8-3,8-4) (07R 7-1,7-2,7-3,7-4,7-5) (07L 7-1,7-2,7-3,7-4,7-5) (06R 6) (06L 6) (05 5))

Looks like that's (not) the problem.

Try doing a DXFIN of 10R, 10L, 11 & 12 one at a time, see if there's an issue with one of those .dxf's ?

ECCAD

Re: Help in Customizing Lisp Code

clerigoaaron — Sun, 06 Oct 2024 23:02:28 GMT

@ec-cad Here is the error message. im not sure why it only appears for 11 onward. They all have the same property in excel

Also no problem at all using DXFIN for each one

Re: Help in Customizing Lisp Code

ec-cad — Sun, 06 Oct 2024 23:56:14 GMT

Hmm,

That's interesting. What's in the variable 'dxfFiles' ??

Do a !dxfFiles at the Command Line, or (princ dxfFiles) to see what's in there.

It (should) contain a list of all the .dxf files in the folder chosen.

If that contains all the .dxf's, then I don't have a clue... why some work and the

others do not.

ECCAD

Re: Help in Customizing Lisp Code

clerigoaaron — Mon, 07 Oct 2024 03:25:46 GMT

@ec-cad same I am stumped. It does contain all dxf files

Re: Help in Customizing Lisp Code

clerigoaaron — Mon, 07 Oct 2024 22:51:54 GMT

Testing another set of data. Everything was generated except pipe 24. I checked the individual dxf and everything even the excel properties of the cell. I cant think of any reason why #24 was the only one missing. Also no error code for this it just somehow skipped it. But trying the lisp on 24 only showed again the error of not being on the source list.

Re: Help in Customizing Lisp Code

ec-cad — Mon, 07 Oct 2024 17:56:02 GMT

In looking at the code again, I don't see an issue that would cause a random misfire.

However, potential exists for these lines that use 'f' for a variable name. F is usually reserved

for 'False.

So try changing these (3) lines to be 'fil' rather than 'f' alone.

1. (Defun c:MakeThisHAppen ( / _Entnext ExtractFromXls el moveAll fileName XL-App Workbooks exData f textObj moveAll)
2. (if (Setq f (Assoc (strcase (vl-filename-base fname)) exData))
3. (list '(0.0 -45.0 0.0) 12 (strcat "Pipe no: " (Cadr f))

ECCAD

Re: Help in Customizing Lisp Code

ec-cad — Mon, 07 Oct 2024 19:31:00 GMT

One other way might be to change:

(if (setq fil (Assoc (strcase (vl-filename-base fname)) exData))

To be:

(if (member (strcase fname) exData)

Try that.

ECCAD

Re: Help in Customizing Lisp Code

clerigoaaron — Mon, 07 Oct 2024 23:00:13 GMT

@ec-cad changing f to fil gave the same result of missing dxf files. Error says "not found in source list".

Using the (if (member (strcase fname) exData) resulted in all dxfs having error of "not found on source list"