Replacing attribute values via an excel file

[jgardner.79905]

I am trying to see if there is a way to have a lisp routine will find all instances of a particular block and replace the attribute value by looking up the existing attribute value in an excel file and replace it with the new value in the excel file in the next column over. I currently have the routine built that will create a selection set of all instances of this block, run a repeat function to check and replace each attribute value but it's based on a conditions function which means I would have to type about 200 conditions. Can anyone help?

[alanjt]

I remember reading where someone had posted something like this over at CADTutor.
http://www.cadtutor.net/forum/index.php

Search there.

[jgardner.79905]

Just to be a little more clear on what I hope can be done...I would like my routine to get the existing attribute value of a particular block, go to a specified excel file and look that value up in a specified column (column "A") and return the value from the next column over (column B) and overright the old attribute value with the value it found from column B. I followed the link in the post above but had no luck. Can this even be done?

[jgardner.79905]

Thanks anyway, I figured it out!

[alanjt]

Good deal.
You should share your findings. Might help someone else down the line.

[CadDog]

Good deal.
You should share your findings. Might help someone else down the line.

Ya, like me...

[jgardner.79905]

Sorry everyone I was on vacation. I have attached what I came up with. It could use some cleaning up but it worked like a champ in a crunch. Combine the scripting i have pasted below with Terry Miller's routine "GetExcel". Let me know if you have any questions.

Special thanks credit to Terry Miller for his "GetExcel" routine.

Jason


Script:

(defun c:redeftag (/ Row# OrigCol DestCol ExcellFile ExcellTab OrigList DestList BlkssSelection BlkintCount BlkEnt att attdata attnum NewVal)
  
  (setq Row# "1000"); <-- Set Max Row Value
  (setq OrigCol "A"); <-- Set Key Column Value
  (setq DestCol "B"); <-- Set Destination Col Value
  (setq ExcellFile "P:\\157 West 57th Street - TCG081113\\99 - DWGS\\_Working DWGs\\05_Schedules\\AV\\Master Equipment Schedule\\Master_AV Equipment Schedule.xls"); <-- Referenced Excel file
  (setq ExcellTab "Sheet1"); <-- Specify Tab Name within Workbook
  (setq OrigList (GetExcel ExcellFile ExcellTab (strcat OrigCol Row#))); <-- Set Key Column Max Range 
  (setq DestList (GetExcel ExcellFile ExcellTab (strcat DestCol Row#))); <-- Set Destination Column Max Range 

  (setq BlkssSelection (ssget "x" (list (cons 0 "INSERT")(cons 2 "Anno_EqpmTag_AudVid_A1")))); <-- Selection of all desired block

  (repeat (setq BlkIntCount (sslength BlkssSelection))
    (setq BlkIntCount (1- BlkIntCount)
	  BlkEntSelection (ssname BlkssSelection BlkIntCount))

    (setq blkent BlkEntSelection
	flag nil
	att (entnext blkent)
	attdata (entget att)
	attnum (entget (entnext att))
	)

    (if (/= (assoc (cdr (assoc 1 attdata)) Origlist) nil)
      (progn
	(setq NewVal (Getcell (strcat DestCol (itoa  (+ (vl-position (list (cdr (assoc 1 attdata))) OrigList) 1)))))
	(entmod (subst (cons 1 NewVal) (assoc 1 attdata) attdata))
	(command "attsync" "n" "Anno_EqpmTag_AudVid_A1")
	)
      (princ)
      ); end if
    ); end repeat
  (gc)
  )

[RobertB]

Script:

(defun c:redeftag (/ Row# OrigCol DestCol ExcellFile ExcellTab OrigList DestList BlkssSelection BlkintCount BlkEnt att attdata attnum NewVal)
...
  (gc)
  )

BTW, that is not a "script". AutoLISP or Visual LISP code, yes.

[steveo]

Sorry everyone I was on vacation. I have attached what I came up with. It could use some cleaning up but it worked like a champ in a crunch. Combine the scripting i have pasted below with Terry Miller's routine "GetExcel". Let me know if you have any questions.

Special thanks credit to Terry Miller for his "GetExcel" routine.

Jason


Script:

(defun c:redeftag (/ Row# OrigCol DestCol ExcellFile ExcellTab OrigList DestList BlkssSelection BlkintCount BlkEnt att attdata attnum NewVal)
  
  (setq Row# "1000"); <-- Set Max Row Value
  (setq OrigCol "A"); <-- Set Key Column Value
  (setq DestCol "B"); <-- Set Destination Col Value
  (setq ExcellFile "P:\\157 West 57th Street - TCG081113\\99 - DWGS\\_Working DWGs\\05_Schedules\\AV\\Master Equipment Schedule\\Master_AV Equipment Schedule.xls"); <-- Referenced Excel file
  (setq ExcellTab "Sheet1"); <-- Specify Tab Name within Workbook
  (setq OrigList (GetExcel ExcellFile ExcellTab (strcat OrigCol Row#))); <-- Set Key Column Max Range 
  (setq DestList (GetExcel ExcellFile ExcellTab (strcat DestCol Row#))); <-- Set Destination Column Max Range 

  (setq BlkssSelection (ssget "x" (list (cons 0 "INSERT")(cons 2 "Anno_EqpmTag_AudVid_A1")))); <-- Selection of all desired block

  (repeat (setq BlkIntCount (sslength BlkssSelection))
    (setq BlkIntCount (1- BlkIntCount)
	  BlkEntSelection (ssname BlkssSelection BlkIntCount))

    (setq blkent BlkEntSelection
	flag nil
	att (entnext blkent)
	attdata (entget att)
	attnum (entget (entnext att))
	)

    (if (/= (assoc (cdr (assoc 1 attdata)) Origlist) nil)
      (progn
	(setq NewVal (Getcell (strcat DestCol (itoa  (+ (vl-position (list (cdr (assoc 1 attdata))) OrigList) 1)))))
	(entmod (subst (cons 1 NewVal) (assoc 1 attdata) attdata))
	(command "attsync" "n" "Anno_EqpmTag_AudVid_A1")
	)
      (princ)
      ); end if
    ); end repeat
  (gc)
  )

Thanks for info...... works perfect !!!
Steve

[emanski]

why this GetExcel lisp routine is not working here in my Autocad 2010 is this can only use in older version?