Excel spreadsheet used to update an AutoCAD block picture.

[davidgeorge212]

I don't know if this is possible but have to ask. If I had an Excel spreadsheet sheet that had a list of part numbers, could I Import that into AutoCAD so that it would load premade 3d blocks where I want it to.

For example I have a warehouse drawing with specific parts stored in specific locations. I have block drawings already made for each part. I have an Excel sheet with a list of part numbers. Is there a way I can import the Excel sheet and have it populate my warehouse drawing with the part drawing blocks in their perspective locations?

 

Thanks!

[BIGAL]

There is a few different ways to do this, i do something similar which is our offices staff images showing where they are located, each image.jpg has a unique Id (phone no) the routine finds the phnum and inserts the image at this spot.

 

You can assign a x,y in the excel or use the method I suggested. The code can be read the excel, read a csv, or run a script.

 

Bottom line must have some form of linking block to a location.

 

Provide some more detail maybe dwg and excel.

[davidgeorge212]

That sounds like what I want to do. I have a list of locations that just go in sequential order 1,2,3... I am just not sure how to go about setting this up in autocad,. Do I just need to import the Excel file? I guess I just need to play around with it to see if I can get it to work.

[BIGAL]

If you Have a simple block which has a attribute as a number then you can find all the blocks and insert a new block at this location by matching the number 12=part52 13=part47.

 

an example

 ; [color="red"]make a selection set of block name  phnum[/color]
(setq ss1 (ssget "x"  (list (cons 0 "INSERT") (cons 2 "phnum")))) 
; [color="red"] how many blocks[/color]
(setq len (sslength ss1))
(setq x 0)
; [color="red"]repeat the process for each block[/color]
(repeat len 
; [color="red"]et each block 1 at a time and retrieve its attrubutes[/color]
(foreach att (vlax-invoke [color="red"](vla[/color]x-ename->vla-object (ssname SS1 x )) 'getattributes) 
; [color="red"]test block tagname is phnum[/color]
       (if (= "PHNUM" (strcase (vla-get-tagstring att)))  
       (progn
; [color="red"]get the number from the atrribute[/color]
(setq blknum (vla-get-textstring att))
; [color="red"]get the insertion point[/color]
     	(setq inspt (vla-get-insertionpoint att))
       )
)
)
; [color="red"]name of image to be inserted change xxxx to correct directory[/color]
(setq name (strcat "P:\\xxxxx\\" blknum ".jpg"  ))
(princ (strcat "\n" name))
; [color="red"]add the raster image name insertion pt scale rotation[/color]
(vla-AddRaster mspace name inspt 1.5 0.0) 
)

Edited September 24, 2017 by BIGAL

[davidgeorge212]

I may be digging myself in a hole here. I guess programming code is not something I have ever added to AutoCAD before. Where would I actually insert the code, in AutoCAD in each block, or do I need some plugin/ extra software for the code to be read?

 

I appreciate all the help

 

 

If you Have a simple block which has a attribute as a number then you can find all the blocks and insert a new block at this location by matching the number 12=part52 13=part47.

 

an example

(setq ss1 (ssget "x"  (list (cons 0 "INSERT") (cons 2 "phnum"))))  ;block name is phnum
(setq len (sslength ss1))
(setq x 0)
(repeat len
(foreach att (vlax-invoke (vlax-ename->vla-object (ssname SS1 x )) 'getattributes)
       (if (= "PHNUM" (strcase (vla-get-tagstring att)))  ; tagname is phnum
       (progn
(setq blknum (vla-get-textstring att))
     	(setq inspt (vla-get-insertionpoint att))
       )
)
)
;(setq blkval (rtos blknum 2 0))
(setq name (strcat "P:\\xxxxx\\" blknum ".jpg"  ))
(princ (strcat "\n" name))

(vla-AddRaster mspace name inspt 1.5 0.0) 
)

[BIGAL]

I have added comments to above code, to test make a block with an attribute and put a number in it, save an image as that number. Change the xxxx to your directory name where the image is and the phnum to your block name and see if it works.

 

You only have to save the code to a text file, use Notepad, copy and paste but saves as a .LSP not TXT, you can then use "Appload" to load the file and run Or type (load "myfile") on command line Or drag and drop from explorer Or from a menu.

 

Ps for (Load filename) filename must be in the Autocad support paths and in older versions of Autocad must be a trusted location to avoid the alert messages.

 

You need to post a dwg and excel to get an idea of what you want.

[SLW210]

I have moved your thread to the AutoLISP, Visual LISP & DCL Forum.