Creating a drawing register in Excel using automation and AutoCAD title block

[grahamd_41]

This seems to be a very widely asked question with many people wanting different solutions. After several days of searching I find myself asking the question here.

 

I work for a small electrical design company. We work with many AutoCAD drawings which require transmittal back and forth to customers. With each set of drawings that we send, we send a document listing all the drawings (a drawing register). This is pretty common practice.

 

Currently we have to manually input the drawing numbers, titles, revision numbers etc. in to an Excel spreadsheet template. What I would like to do is semi-automate this process, preferably using Excel's VBA, or at a later date MS Access.

 

I have some experience in using VBA, accessing other xls files on the server and collating information, but have never used it to access an AutoCAD dwg file.

 

In short, what I have/want to do is as follows:

• I have an Excel template that lists will list all the drawings for transmittal.
  
• I have hundreds of AutoCAD drawings which contain a title block with the following attributes: Drawing_number, Title_1, Title_2, Title_3, Revision (and more, but these are the main required attributes for the drawing register)
  
• I want to run the excel file, select (through the use of a browse file command, which I am familiar with) the drawings I wish to add to the list, extract the information contained in the above attributes, write this information to my desired cells in the drawing register.
  

I am fairly competent at programming and trained in C, C#, C++ at university (a few years ago) so my understanding is okay. I am just struggling with acquiring the code to access AutoCAD attributes. My understanding is that I need to use a library to prevent me opening AutoCAD each time I read a file, but again I'm unsure of how to do this.

 

I do not wish to extract the information from the AutoCAD files individually as this would be far too lengthy and I want to avoid the use of lisp as I wish to enclose the whole package of creating a drawing register for other users in the company who have very little/no experience of programming code.

 

I would also like to avoid datalinks, this isn't an option, and I don't wish to be able to modify the content of the AutoCAD title block using Excel (unless this is a very simple addition, as it isn't required at this stage)

 

Thank you in advance.

Graham

[Patrick Hughes]

The DATAEXTRACTION command might satisfy your need. I've only played around with it so I'm not certain it will fulfill everything you need.

[BIGAL]

As you know about C etc Search for .net code to retrieve attribute information without opening a dwg, there should also be the code for updating excel once you have this info.

 

This google gave lots of hints autocad .net code for attribute retrieval I dont do .net so can not help further. You can download free MS Visual studio for compiling and there is plenty of people here who can help with correct library links.

[grahamd_41]

The DATAEXTRACTION command might satisfy your need.

Hi Partrick, unfortunately I believe DATAEXTRACTION requires each drawing to be loaded individually to work. I have seen many tutorials to use this technique and if I only had a few drawings it would be great, but as said I could have several hundred drawings. Thank you for your reply.

As you know about C etc Search for .net code to retrieve attribute information without opening a dwg, there should also be the code for updating excel once you have this info.

Hi BIGAL, I had considered this option, but was trying to avoid this route in favour of a more basic route that could utilise purely Excel's functionality. I think however I will look in to this and appreciate the Google search criteria. Thanks for your reply.

[Patrick Hughes]

graham,

 

 

It seems DATAEXTRACTION does not "open" the individual drawings in AutoCAD. Within the Data Extraction application of course it must "open" the file to read the contents. Maybe try it on a small sample of drawings.

[BIGAL]

Autocad can open a excel session and have two way data communication you can do this with lisp, Vlisp, VBA or say .net the only problem is getting the right language that allows dwg database interrogation without opening a dwg. In saying this you will be suprised how fast you can open a dwg get the info close it update excel got to next dwg and so on.

 

Like the old saying crawl 1st then walk then run find the bits you want

retrieve attributes

open excel session

pass data to excel

close and open new dwg

repeat pass data to excel

 

This question has been asked many times before here I am suprised no one has posted the solution it may take a little time to find using Excel as the search key.

[dheather]

Hello,

 

 

I find myself in the same situation with hundreds of drawings to log and a differing and lower version of CAD to the designers. Did you ever resolve this?

 

 

Any help would be greatly appreciated.

 

 

Thank you

[TheCADnoob]

DATAEXTRACTION is pretty slick id go that way if you could. I have used it on large file sets (6000 dwgs) and it can take long ass time if you are extracting a lot of data. Also if there is a corrupt file it just dumps the process without indicating which file is the problem which is a pain.

 

one of the other ways i have done this through lisp and VBA. I used VBA to create a file list. I started with a script by John Walkenbach and hacked it up to get what i wanted out of it.

 

http://spreadsheetpage.com/index.php/site/tip/getting_a_list_of_file_names_using_vba/

 

 

For the lisp portion i used some of Jefferysanders code CAD2FILE and hacked it up to look for a trigger varible in a file and only take the data i wanted.

http://www.jefferypsanders.com/autolisp_cad2file.html

I trimmed out to only read attributes of the titles i wanted and i made it do it automatically so i could loop it in a batch wrapper.

 

The batch wrapper lets you select a block of drawings and loops through those drawings. It toggles a variable i put in a file to tell the modded CAD2FILE whether or not to trigger with each new drawing opened. I can provide this if there is an interest but i would whole heartily recommend DATAEXTRACTION over this as if i knew that DATAEXTRACTION existed i would have never hobble together that mess of code.

[KiLLiNG-TiME]

If you use the sheet set manager you can just insert sheet list on a sheet to create a drawing register very handy very easy.

[chauncy274]

DATAEXTRACTION is pretty slick id go that way if you could. I have used it on large file sets (6000 dwgs) and it can take long ass time if you are extracting a lot of data. Also if there is a corrupt file it just dumps the process without indicating which file is the problem which is a pain.

 

one of the other ways i have done this through lisp and VBA. I used VBA to create a file list. I started with a script by John Walkenbach and hacked it up to get what i wanted out of it.

 

http://spreadsheetpage.com/index.php/site/tip/getting_a_list_of_file_names_using_vba/

 

 

For the lisp portion i used some of Jefferysanders code CAD2FILE and hacked it up to look for a trigger varible in a file and only take the data i wanted.

http://www.jefferypsanders.com/autolisp_cad2file.html

I trimmed out to only read attributes of the titles i wanted and i made it do it automatically so i could loop it in a batch wrapper.

 

The batch wrapper lets you select a block of drawings and loops through those drawings. It toggles a variable i put in a file to tell the modded CAD2FILE whether or not to trigger with each new drawing opened. I can provide this if there is an interest but i would whole heartily recommend DATAEXTRACTION over this as if i knew that DATAEXTRACTION existed i would have never hobble together that mess of code.

 

This is an old thread, but I wanted to post that I agree with noob here. DATAEXTRACTION works great and very quickly in my experience, and shoots out the data in a very readable and editable format. Also it has the plus side of not having to open up every drawing. I have 1000 drawings through it in 5 minutes or less. And never any mistakes.

 

Also, if you are looking for a way to update the drawings from the drawing list info, look at Lee-Mac's "Update Title-Block Info" Lisp routine.

 

http://www.lee-mac.com/updatetitleblock.html

 

It works wonderfully and when we bump up our drawings from "approval" issue to "construction issue" we can run it through all the drawings in the same amount of time it takes to publish them.