How to import lookup table value from excel cell

[vijay.shankar3BXDM]

hello forum,

I have a task to import a value from excel cell to an lookup table value named as "Tag1" which to be changed.

I have placed a sample code below. I am not sure how to do the process. Please make changes in the below code.

(setq cell (vlax-variant-value (vlax-get-property rng 'Item (vlax-get-property xlCell "Row") (+ 6 (vlax-get-property xlCell "Column")) ) ) ) (vl-some '(lambda (x) (if (= (vlax-get x 'tagstring) "Tag1") (vla-put-textstring x (RTOS (vlax-variant-value (vlax-get-value cell 'value2)) 2 2 )))) (vlax-invoke obj (get-properties "Tag1" BlockRefObj)))

[Sea-Haven]

That is about a 1/3 of what you need, you must open a link with excel 1st, you can then simply  GETCELL with a known cell address like "A6"

Have you looked at getexcel.lsp a good starting point. 

When asking this type of questions need the following to be posted.

1 dwg before 

2 dwg after

3 excel file

[vijay.shankar3BXDM]

Dear Sea,

Sorry I was actually placed a bit of code and requested.

Now I am placing the before and after file and the excel file.

Please have a review. let me know how to apply the lookup value(Lookup1) in the drawwing from excel. 

[Sea-Haven]

So you just want 2 layouts per column A, matching the number in column B, one match top number, one match bottom number.

[vijay.shankar3BXDM]

Yes, Could you please provide the lisp code for that.

 I need the lookup value to be updated as per column2 based on the value in the first column which is layout number. 

You can see the samples attached.

[_gile]

Hi,

To read/write in an Excel sheet, you can use (or get inspiration from) the code of this reply.

[vijay.shankar3BXDM]

Hi this is not suitable for my task. as i need to import to an particular attribute in the excel. You just send me the simple export and import. It won't work.

[_gile]

This suits the "read Excel" part of your task. You just have to populate the attributes of each Layout block references with the got data. You'll find many examples on how to set attribute values in this forum.

[Sea-Haven]

You have a couple of separate  posts now and Gile and I have been responding to you, the get, put Acad <--> Excel programming requires that you have a bit of experience when coding in lisp, it is not rocket science, but once you get the hang it can be very simple. The hardest bit is to do with is excel open.

You need to take a step back and study the functions that are in the code being provided. Get to understand how to convert say a A3 to 1 3 for get put, and the opposite 1 3 to A3. Here are 2 programs Getxecel.lsp is where I started and then wrote my own version with some extra features. 

You need to understand how to do stuff like (defun putcell (cellname val1 / ) but cellname starts as row and column 

values in Acad. So have to use an extra function to do the conversion then pass the value. That is code by Gile.

A minimum bit of code open excel and have a blank worksheet.

(defun c:testexcel ( / )
(defun putcell (cellname val1 / )
(setq myRange (vlax-get-property  (vlax-get-property myxl "ActiveSheet") "Range" cellname))
(vlax-put-property myRange 'Value2 val1)
)

(setq myxl (vlax-get-object "Excel.Application"))
(vla-put-visible myXL :vlax-true)
(vlax-put-property myxl 'ScreenUpdating :vlax-true)
(vlax-put-property myXL 'DisplayAlerts :vlax-true)

(putcell "A3" "123")
(putcell "B3" "456")
(putcell "C3" "789")
(princ)
)

[hosneyalaa · Accepted solution]

hi

try

(defun c:QQQQQTEXTFINDly (/ CELL CELL1 EXCDATA FILENAME I ICOL IROW IROWS LW-LST MAKE_LW NUMBER NUMBERSTR NUMBERSTR1 OBJ OBJ2 RNG SSVP1 SSVP22 VP1 VP2 XLAPP XLBOOK XLCELL XLFRANGE XLRANGE XLRANGEC XLSHEET)
  (vl-load-com)
  
  
  (if (setq fileName (getfiled "Select Excel file to find cell address :" (getvar "dwgprefix") "xlsx;xls" 16))
    (progn
      
      
      
      (setq xlApp (vlax-create-object "Excel.Application"))
      (vlax-put-property xlApp "Visible" :vlax-true)
      (setq xlBook (vlax-invoke-method (vlax-get-property xlApp 'WorkBooks) "Open" fileName ) )
      (vlax-invoke-method xlBook "Activate")
      (setq xlSheet (vlax-get-property (vlax-get-property xlBook "WorkSheets") "Item" 1) )
      
      (setq xlRangeC (vlax-invoke-method xlSheet "Activate") )
      (setq xlRange (vlax-get-property xlSheet "Range" "a2:b10"))
      (vlax-invoke-method xlRange "Select")
      (setq iCol (vlax-get-property xlRange "Column"))
      (setq iRows (vlax-get-property(vlax-get-property xlRange "Rows") "Count" )iRow  (vlax-get-property xlRange "Row") )
      (setq rng (vlax-get-property xlApp 'Cells))
      
      (setq
	ExcData	 (vlax-safearray->list
		   (vlax-variant-value
		     (vlax-get-property xlRange 'Value)
		     )
		   )
	)
      
      (setq
	ExcData (mapcar
		  (function (lambda (x)  (mapcar 'vlax-variant-value x) ))
		  ExcData
		  )
	)
      
      
      ;(setq lw-lst nil)
      (foreach pt ExcData
	(setq lw-lst (append (list(car pt)) lw-lst))
	
	)
      
   
      (setq make_lw (reverse (vl-remove nil lw-lst)))
      
      
      
      ;;Group-1.1
      (setq i 0)


      
      (repeat (length make_lw)


	
	(setq numberstr (strcat "Group-"  (RTOS (setq number (nth i make_lw)) 2 0 )".1" ))
	(vl-cmdf "._layout" "c" "00" numberstr)   
	(vl-cmdf "._layout" "s" numberstr "._pspace")
	(setvar "ctab" numberstr)
	

	(setq ssvp1 (ssget "x" (list (cons 0 "insert") (cons 410 numberstr) )))
	(setq vp1 (ssname ssvp1 0))
	(setq obj (vlax-ename->vla-object (ssname ssvp1 0)))

	(setq xlCell (vlax-invoke-method
		       xlRange
		       "Find"
		       (vlax-make-variant number)
		       xlFRange
		       -4163
		       1
		       1
		       1
		       nil
		       nil
		       )
	      )
	
	
	(setq cell (vlax-variant-value (vlax-get-property rng 'Item (vlax-get-property xlCell "Row") (vlax-get-property xlCell "Column")    )     )  )
	(vl-some '(lambda (x) (if (= (vlax-get x 'tagstring) "HeadNumber") (vla-put-textstring x (RTOS (vlax-variant-value  (vlax-get-property cell 'value2)) 2 2 )))) (vlax-invoke obj 'getattributes))


	
	(setq cell1 (vlax-variant-value (vlax-get-property rng 'Item (vlax-get-property xlCell "Row") (+ 1  (vlax-get-property xlCell "Column")  )    )     )  )




	(vl-some '(lambda (x) (if (= (vla-get-PropertyName x) "Lookup1") (vlax-put x 'Value (RTOS (vlax-variant-value  (vlax-get-property cell1 'value2)) 2 2 )))) (vlax-invoke obj 'GetDynamicBlockProperties))
	
	
	
	
	
	(setq numberstr1 (strcat "Group-"  (RTOS (setq number (nth i make_lw)) 2 0 )".2" ))
	(vl-cmdf "._layout" "c" "00" numberstr1)
	
	(setvar "ctab" numberstr1)
	
	(setq ssvp22 (ssget "x" (list (cons 0 "insert") (cons 410 numberstr1) )))
	(setq vp2 (ssname ssvp22 0))
	(setq obj2 (vlax-ename->vla-object (ssname ssvp22 0)))

	


	(vl-some '(lambda (x) (if (= (vla-get-PropertyName x) "Lookup1") (vlax-put x 'Value (RTOS (+ 1 (- 10 (vlax-variant-value  (vlax-get-property cell1 'value2)) ) ) 2 2 )))) (vlax-invoke obj2 'GetDynamicBlockProperties))
	
	
	
	(setq i (1+ i))




	
	)
      
      )
    )

  
  (princ)
  )

[Sea-Haven]

Not sure how that helps to learn about acad <--> Excel

look at this get excel range by FIXO.

; thanks to Lee-mac for this defun 
; www.lee-mac.com
; 44 is comma 9 is tab 34 is space 58 is colon
(defun _csv->lst58 ( str / pos )
	(if (setq pos (vl-string-position 58 str))
		(cons (substr str 1 pos) (_csv->lst58 (substr str (+ pos 2))))
		(list str)
    )
)

; great get range by FIXO

(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)
)