Extracted attributes from AutoCAD into excel pre-formatted spread sheet

[cadmando-2]

I have multiple drawings we copy drawing number, description and from each drawings, and standard part and its description from the BOM in each drawing. And then paste into Pre-formatted excel spread sheet.

1. Drawing number first, description.

2. Standard parts and its description from the BOM in each drawing.

I’m looking for a way to extract attributes drawing file number and title and to be able to extract standard parts or selected parts and description from BOM in AutoCAD drawing file from mutable drawings into pre-formatted Excel Spread sheet?

I am using AutoCAD mechanical 2018.

Is there away to do this procedure or Lisp routine?

 

http://www.cadtutor.net/forum/images/icons/icon6.gif

1715-143.xls

[Spacepig62]

Hi Cadmando-2

 

Sorry to have taken a while getting back to you - I saw you post both here and AUGI, but our wonderful IT department at work define both sites as Social Media when I post code and blocks posting.

 

I'm nearly finished writing a similar program as yourself that exports attributes from Autocad 2016 into Revit 15 (see my post earlier today) with the invaluable help of dlanorh.

 

I've added some code below that hopefully will give you a pointer on how to do it - I'll be posting my completed program which you should be able to adjust to your own application, but basically the procedure is this -

 

1. Create an attribute extract file

2. Use the attribute extract function within a lisp program to export the attribute info (Drawing number, Drawing title etc) to a comma delimited text file (.csv)

3. Use the Read-line function to read each line of the text file and use the commas to "cut" the line into variables in the lisp file.

4. Push the variables into the correct cell on the correct worksheet in the excel file....

 

(defun c:excelcontrol ()

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;This sets up the autocad  environment to run  Excel;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;


;;;load all the VL commands, then the set the library to Excel
 (vl-load-com)
 (setq tlbfile (findfile "C:\\Program Files\\Microsoft Office 15\\root\\Office15\\Excel.exe"))
 (vlax-import-type-library :tlb-filename tlbfile :methods-prefix "msxl-" :properties-prefix "msxl-" :constants-prefix "msxl-")
;;;Open the excel spreadsheet and find the current worksheet
 (setq xfile "c:\\1715-143.xls")
 (cond 
   ((setq fn (findfile xfile))
     (cond
       ((setq appsession (vlax-get-or-create-object "Excel.Application"))
         (vlax-invoke-method (vlax-get-property appsession 'workbooks) 'open fn)
         (vla-put-visible appsession 0)
       )
     )
   )
   (t (alert (strcat "nCannot locate source file: " xfile)))
 )
 (setq xlapp appsession)
 (setq rng (msxl-get-activesheet xlapp))






;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;This is how you set the cell  number;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;


;;;Setq the cell reference to put the drawing number into (cell A10)
 (setq Data1_row 10)
 (setq Data1_column 1)
 (setq cell_detail_1 (vlax-variant-value (msxl-get-item (msxl-get-cells  rng) (vlax-make-variant Data1_row) (vlax-make-variant Data1_column))))
;;;Setq the cell reference to put the drawing title into into (cell H10)
 (setq Data2_row 10)
 (setq Data2_column 
 (setq cell_detail_2 (vlax-variant-value (msxl-get-item (msxl-get-cells  rng) (vlax-make-variant Data2_row) (vlax-make-variant Data2_column))))







;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;This is how you put the information in the  spreadsheet;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;


;;;Get the drawing number and upload it into the excel spreadsheet
 (msxl-put-value2 cell_detail_1 drawing_number)
;;;Get the drawing  and upload it into the excel spreadsheet
 (msxl-put-value2 cell_detail_2 drawing_title)







;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;This saves and closes the  spreadsheet;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;;Save the modified workbook
 (setq workbook (vlax-get-property xlapp "ActiveWorkbook"))
 (vlax-invoke-method workbook "Save")
;;;Close excel, clear the memory and exit quietly
 (vlax-invoke-method xlapp "Quit")
 (vlax-release-object xlapp)
 (gc)
 (princ)
)

Edited May 2, 2018 by SLW210
Added Code Tags

[Spacepig62]

the "Smiley" should be an 8 followed by a )

[SLW210]

the "Smiley" should be an 8 followed by a )

 

Please read the Code Posting Guidelines and have your Code to be included in Code Tags.[NOPARSE]

Your Code Here[/NOPARSE]

=

Your Code Here

[Spacepig62]

Thanks SLW210

 

I couldn't find how to do the paste data code - I'll do it in future...

 

Best regards

 

Spacepig62

[maratovich]

cadmando-2

It's possible. But much depends on the title block.

Can you attach an example of the title block .dwg?

[cadmando-2]

This is title with BOM that is generated by balloon command that inserts balloon and add BOM. Then you have to edit each BOM line and enter the value for each line. not very productive, but it what we have. I even uploaded the BOM.dwg and lisp routine that inserts the balloon and BOM

D-TITLE.dwg

bom.dwg

dball.lsp

DBOM.dwg

[maratovich]

We need a real file with several title blocks for verification.

Your example is empty.

[cadmando-2]

Thanks

I trying to work through it when I have time. just had big project dumped on me and no time to work on this.

Never fails when I have some down time to work on some custom CAD stuff, that i'm have to put it off to the side.

it good staying busy, but the guys tried of unnecessary work if the process can be stream lined.

I know just enough lisp to get by, but to write my own code after 30 years of CAD. I'm not there yet. any suggestion on books or web site. I can write scripts and modifie some lisp files.

I just purchased "AutoLiSP Programming principles and Techniques" by Rod Rawls/Mark Hagen, "AutoCAD AutoLISP" by Trevor Bousfield, AutoCAD Expert's Visual LISP by Reinaldo N. Togores.

[cadmando-2]

D-Title Block

1715-0143-00-R2.dwg

[BIGAL]

Had a look at your dwg and pretty straight forward. May need more than 1 function to match the task, the bom is diffrent to say titleblock.

 

In saying that, a method would be pick a block, pick a slection retrieve attributes either all or what you want, then do something export to csv txt file excel etc.

 

When you pick a block you can get at the attributes also. As a 1st step it would be extract attributes to say csv then go more advanced and put into excel cells.

 

Wether it be mulitiple lines of block attributes or a title block the method is the same just how many.

 

I am sure if you google for "title block to excel" you will find example code.

 

Here is something to get you started. Have your text screen displayed at same time.

 

(vl-load-com)
(defun c:test ( / ans obj)
(setq ans " ")
(while (setq obj (vlax-ename->vla-object (car (entsel "\nPick a block"))))
(foreach att (vlax-invoke obj 'getattributes)
(princ (strcat "\n" (vla-get-textstring att) " - " (vla-get-tagstring att)))
)
(princ "\n")
(setq ans " ")
)
)

Also this for multiple blocks

(setq ss1 (ssget  (list (cons 0 "INSERT") (cons 2 bname))))
(repeat (setq x (sslength ss1))
   (foreach att (vlax-invoke (vlax-ename->vla-object (ssname SS1 (setq x (- x 1)) )) 'getattributes)

 

http://www.cadtutor.net/forum/showthread.php?104147-Coding-issue-exporting-info-automatically-from-Autocad-2016-to-Excel-15

Edited May 4, 2018 by BIGAL

[cadmando-2]

thank for info and lisp routine, when i get a chance between projects I try it out.

Thanks again

[cadmando-2]

tried the top code and gave me an "Pick a block ; error: bad argument type: lentityp nil".

[BIGAL]

I added the (vl-load-com) as a precaution, I just reopened your dwg and picked 3 lines in your parts list all appear in the text screen press F2 an error occurs when I pick nothing or press enter to exit. I need to tidy up the exit.

 

Pick a block
5 - ITEM
SHFT-31IZ-112 - PART_NO
SHAFT, IDLER, %%C1 15/16" (CTC) - DESCRIPTION
10 - QTY

Pick a block
6 - ITEM
--- - PART_NO
HEX BOLT, %%C3/4-10UNC X 3" LG. - DESCRIPTION
20 - QTY

Pick a block
7 - ITEM
1715-STD-043 - PART_NO
SHAFT, TAKE-UP, %%C1 15/16" - DESCRIPTION
10 - QTY

Pick a block
; error: bad argument type: lentityp nil