Budget estimating - link Attribute information to a spreadsheet

[fww84855]

Hi everyone,

I am wondering if there is a way to give drawings attributes like cost or pricing information and have those attributes linked into a spreadsheet? I just took an introductory course to GIS and found it interesting how we could give attributes to the objects being displayed and then make reports from the attributes. Is there any way of doing this sort of thing with Autocad and Excel? I would sure find it helpful when making a budget or estimate for a project. I would like to associate costs to the drawings or blocks of equipment I use in a layout, then make a spreadsheet of those costs, and have it change with the layout.
Can anyone lead me in the right direction or is this impossible?

Thanks in advance for any feedback

[Mike.Perry]

Hi Frank

Please note I've *moved* this thread from the ACAD 2005 General (http://forums.augi.com/forumdisplay.php?f=58) Forum to this one as I feel this particular Forum is a more appropriate place for such a topic.

Thanks, Mike

Forum Moderator

[Mike.Perry]

Hi

Have a browse of the following threads -

dbconnect (http://forums.augi.com/showthread.php?t=4827)

tracking drawings (http://forums.augi.com/showthread.php?t=4907)

Automatic BOM Generation (http://forums.augi.com/showthread.php?t=7456)

Have a good one, Mike

[jaberwok]

Hi Frank.
In AutoCAD Help, look up "blocks/attaching atributes to". Then look up "ATTEXT command". This will give you an idea of what is possible and a starting point.

[fww84855]

Thanks for the info
Now I just have some reading to do.
This is sure a great way of getting answers to questions.
There is just so much information out there now a days, its hard sometimes to know where to start.

Thanks again

[fww84855]

Well I started to read through some of the VBA articles and found it to be long and confusing. I tried to make a connection with Excel and launch it but got errors. Only after feeling like a computer programmer. There has to be an easier way to put an attribute table into a spreadsheet from Autocad. This is too time consuming.

Can anyone help me out?

Thanks

[RobertB]

Have you tried extracting to a .csv file, which Excel will read?

[fww84855]

I did, but when I tried using the template file I made in Word I got a fatal error.
I don't have Notepad on my station so I used Word and saved as a .txt file. When I attempt to export the blocks using my template file I get an error message back about not able to open the template file then a fatal error: Unhandled access violation reading 0x0038 exception at 6517e9c0h. Whatever that means.
Attached is my template file. Did I do something wrong there?

Thanks

[Mike.Perry]

Hi

You need to use a ASCII Text Editor or at least a piece of Software that will SaveAs "pure" ASCII format for the creation of your Template file (Word does not SaveAs "pure" ASCII TXT file, it leaves a lot of rubbish laying about).

A search on the web will turn-up a lot of freeware ASCII Text Editors.

Have a good one, Mike

[slayer913]

I've been on the path of creating an interactive CAD environment myself, and have had to do it more or less all alone. If you check out some of my previous questions, you'll see that I've asked about integrating Access, Excel, and CAD, and that really no one has had any input here! (sarcastically said):razz: :razz: :razz::screwy: Seriously though, you'll find some great information there along these same lines.

What I've discovered:

1) Access and AutoCAD actually go rather well together, but take a bit of programming and of course forethought to develop. I decided to go with Access so that I could move beyond Excel's spreadsheets and jump right to complete enterprise organization and data solution automation.

2) Tool palettes in 2006 are awesome, and with attributes and dynamic blocks, one could essentially have all of their information stored in very few blocks and linked to an Access database at the same time.

3) Check out the VBA course in AUGI's educational seminars. If the past version called something close to "Integrating Access with AutoCAD through VBA" isn't on file, I can post it (a three part pdf format series).

4) To specifically solve the problem you're dealing with, as I was, I planned out the following:
-Create a part inventory in Access for all parts we would use (with cost, model#, etc)
-Create relations between Access tables for costs, estimates, and scheduling
-Create CAD blocks to represent the necessary equipment/parts
-Link via VBA programming and OLE integration, Access database to CAD block database
-Utilize Access database forms, reports, and queries to generate BOM's, quotations, weld factors, time estimates, inventory forecasts, job site equipment and supplies estimates, Purchase orders directed to appropriate vendors, even links for printing installation/configuration manuals from our reference library, etc!

This is a 3D modeling system, requiring that only one design is every needed, and the database does the rest.

Hope this is helpful!

Albert

[Rick.B]

You might want to look at extracting attribute data once more; this time use the command EATTEXT or select the Attribute Extract tool from the Modify II toolbar. It will walk you through the process and prompt you to export a template file for later use.

[Mike.Perry]

If you check out some of my previous questions, you'll see that I've asked about integrating Access, Excel, and CAD, and that really no one has had any input here!Hi Albert

Sorry the above really grates me (or am I reading it totally wrong, I truly hope that is the case).

I take it you are referring to "Automatic BOM Generation (http://forums.augi.com/showthread.php?t=7456)".

If I have the above correct, may I please suggest you directly contact all of the people that kindly gave up their time to offer assistance / help in that thread, and let them know they input here! has been of no help to yourself.

I really do hope I misunderstood your above comment, but the more I have read it, the more I find it insulting to those people that gave up their time in a effort to help you.

Thanks, Mike

[fww84855]

Thanks Albert,

So it sounds to me like you don't use attributes in the block to store the data, but you set up a data base in Access then link this to the CAD dwgs of the equipment.
Well, is linking this data to the CAD dwgs a difficult process? Do I need a programmer to do this? I'm not very experienced in that. Will it be a long process to set up?
What I'd like to be able to do is make a layout with equipment etc. then produce a report from data of the equipment. Then if I change or revise the layout the reports get updated automatically.

Thanks for the feedback

[fww84855]

Thanks Rick B

I used the EATTEXT command and that was very helpful. It seemed to work rather easily. So now I created a .CSV file of my attributes, how do I import this into Excel?

Thanks for the feedback

[Rick.B]

This is an Excel format file... Just open it up in Excel.

[fww84855]

Thanks Rick

I finally did it.
I thought it should be simple.

Thanks for your help

[slayer913]

Hi Albert

Sorry the above really grates me (or am I reading it totally wrong, I truly hope that is the case).

I take it you are referring to "Automatic BOM Generation (http://forums.augi.com/showthread.php?t=7456)"....

Thanks, Mike
Mike -

Of course I didn't mean to insult anyone. Actually you're right - that is a pretty damning statement when I reread it now. I thought that the "!" would be enough to note a mock tone, but apparently that was not the case. I'll go back and try to edit in a few smilies and the text "sarcastically" at the end of the sentence.

Truth is Mike, between your posts, Richard Binning's post, and all of the others, I was able to dive into my project with a solid set of fins, so to speak. Why would I wish to slap everyone on the face for that? Heh; not the case.

Sorry to anyone who went literal on that statement - my fault for assuming a context that was not apparently clear.

Thanks, Albert

[slayer913]

Thanks Albert,

So it sounds to me like you don't use attributes in the block to store the data, but you set up a data base in Access then link this to the CAD dwgs of the equipment.
Well, is linking this data to the CAD dwgs a difficult process? Do I need a programmer to do this? I'm not very experienced in that. Will it be a long process to set up?
What I'd like to be able to do is make a layout with equipment etc. then produce a report from data of the equipment. Then if I change or revise the layout the reports get updated automatically.

Thanks for the feedback
FWW -

Hey - I did create the programming myself, after climbing the Access, VBA and Java (for interface) curves. If you don't need complete organization integration (I sure not too many do yet), I'd think that using the good old attribute methods would be best. Like the others said, you can link objects to tables now in real time.

I use attributes in all blocks for in-drawing data collection - the programming comes in trying to make Access and CAD bi-directionally associative. CAD attribute says "2 Inch Ball Valve, Vendor, Material, etc", and the Access DB lists project specific data (line #, configuration, ordering information, scheduling, installation resource estimates, etc). Associativity comes in when you modify an object field in one program, the other reflects the change (at least it will when I get to that point). For in drawing schedules, I used to export to Excel the attributes for BOM's and such to put in drawings, and now with '06 you don't even need to get Excel involved - it's all integrated.

Albert

[fww84855]

Albert,

Thanks for the continued feedback.

You mentioned linking objects to tables in real time. What do you mean by this? Does this mean table gets updated automatically when the object changes after a save? Like when using xref. Are there commands for that or is it programming?

The way I see it now, is that when I make a layout with the attributes in the blocks I export them everytime I want to create a report. Is the report generating all part of 2006?
So once I set up my templates I tell Autocad what report I want and it makes it?
That would be nice.

Thanks again

[Mike.Perry]

Of course I didn't mean to insult anyone. Actually you're right - that is a pretty damning statement when I reread it now. I thought that the "!" would be enough to note a mock tone, but apparently that was not the case. I'll go back and try to edit in a few smilies and the text "sarcastically" at the end of the sentence.

Truth is Mike, between your posts, Richard Binning's post, and all of the others, I was able to dive into my project with a solid set of fins, so to speak. Why would I wish to slap everyone on the face for that? Heh; not the case.

Sorry to anyone who went literal on that statement - my fault for assuming a context that was not apparently clear.Hi Albert

Like I said, I had hoped I was miss reading that particular statement; am pleased to see that is the case....

The exclamation mark "!" for me made it worse, as when used in grammar it is a sign of a sudden loud cry ie. Shouting (but that could be my problem and my over **** use of grammar).

Thanks, Mike

[slayer913]

Albert,

Thanks for the continued feedback.

You mentioned linking objects to tables in real time. What do you mean by this? Does this mean table gets updated automatically when the object changes after a save? Like when using xref. Are there commands for that or is it programming?

The way I see it now, is that when I make a layout with the attributes in the blocks I export them everytime I want to create a report. Is the report generating all part of 2006?
So once I set up my templates I tell Autocad what report I want and it makes it?
That would be nice.

Thanks again
Fww -

Yes - when I say real time, I mean on the fly. The idea of "real time" scheduling is that if you modify a block, the table is modified respectfully on its own.

Let's say you insert a block "Check Valve". So you put this block where it needs to go, and create a BOM which reads "Check valve Qty 1". Now, if you wish to copy the block and put one somewhere else, you do so, and the BOM changes to read "Check valve Qty 2". This is real time updating. 2006 has a semi-real time table system now, in that you can make the changes and refresh the table to reflect the changes. But new (different) blocks are not included unless you create another table and select them. Still, vastly more efficient than before.

In pre-2006, the only way I could get this was via programming. A bunch of LISP's and VBA macros are out there that are already created along these lines. 3RD party software packages also take this above and beyond into project databasing, compiled schedules, etc. When you say "report" generating, I'm wondering if you mean a report that you need to use outside of AutoCAD, as opposed to a BOM or schedule on a drawing sheet. If you do need something external, perhaps the best you can easily get is by exporting the attributes to a template spreadsheet like you had mentioned. This isn't too challenging, but requires you to remember every time you make a change to the dwg, to create a new report.

Thanks,
Albert