Change hatch color based on spreadsheet information

[jmb789]

I'm currently using AutoCAD 2014. I have a floor plan already drawn and each individual room already has a seperate hatch created. What I would like to be able to do is somehow make the color of the hatch change to represent the percent completion of each room. I would ideally like to just be able to make an Excel spreadsheet that contains in column A the room title like Kitchen, Bath Room 1, Living Room, etc. and column B would contain the percent completion. So when I change column B from 20 to 30 the color updates. A couple different people will be imputting in to the spreadsheet which is why it I want it to be so simple. What would the best possible way to accomplish this be? My floor plan contains about 60 different spaces and would have about 10 different color to represent the percent completes. Should I first make all the the hatches for each room a seperate block with it's unique name to identify it? And should I also make each percent complete color a seperate layer? I'm not really sure how to go about this but I'm very much willing to learn. Have the ability to do something like this will be a great aide in the future.

[rkmcswain]

I can't think of any built in way to conditionally control an object property, much less based on a value in a spreadsheet.

Having said that, it's entirely possible to do this with a custom routine.

You'll have to decide how and when AutoCAD should go out and read the spreadsheet. It won't just know on it's own when an update occurs.

Are you wanting to write this code yourself, or farm it out to a programmer?

[dbroad]

Other than controlling it by a spreadsheet, most of what you want to do is possible through display themes in AutoCAD Architecture.  Instead you could control it by schedule or property set.

[hwalker]

If you want to do it in Excel. Then in Excel explore the CONDITION statement.

It sort of goes "If cell A1 is 20 then the background colour of B1 should be green"

[jmb789]

As was mentioned I think it will take a specially written code to accomplish this task. I'm currently looking at other articles to see how the code is formatted. I basically need it to look in Column A of a spreadsheet and select a block with the same name. Then change the layer the block is on based off of information in Column B. The colors would be updated bi-weekly and monthly or whenever an updated print was needed. I would choose to run the command manually each time. The biggest need for this is to be able to save time as this will eventually be used to change the layer of over 300 or more blocks. Thanks for all the imput so far. I'm hoping with some more insight and learning I can get to my goal.

[nkhuri]

Did anything ever come of this? I would like to use the same concept in a building plan.

Thanks,

Nick 

[Pierre.ReidUNJK2]

I do have different objective but with the same approach, hatch color with excel value,

• the objective is color code region used by individual working for a specific department or specific project to calculate  department or project sq ft cost per period.
• the objective is to evaluate quantity vacant space available for new employees.
• the objective is to evaluate space use as common support.   

[leeminardi]

An object's color property can be changed with the chprop command.

In the following Excel worksheet and AutoCAD drawing group names are assigned to a room's hatching. This enables the easy selection of an object by name.  Excel is used to create a command that can be copied/pasted into AutoCAD to change the colors.

In the worksheet above, the cell E3 contains the following:

=CONCATENATE("(COMMAND ",CHAR(34),"chprop",CHAR(34)," ",CHAR(34),"g",CHAR(34)," ",CHAR(34),B3,CHAR(34)," ",CHAR(34),CHAR(34)," ",CHAR(34),"c",CHAR(34)," ",D3," ",CHAR(34),CHAR(34),")")

It produces the following output:

(COMMAND "chprop" "g" "ROOM1" "" "c" 2 "")

which tells AutoCAD to use the chprop command and then select group ROOM1 and change its color  ("c") property to 2 (the AutoCAD color yellow). Since the Excel concatenate command uses a quote mark (") to denote a text string and a vlisp statement requires a quote mark to distinguish a text string from a variable, the ASCII character for quote (CHAR(34)) is used to output quote marks when needed.

The Excel function VLOOKUP is used to define the rules for coloring the hatch.  Note that cell D3 contains:

=VLOOKUP(C3,$A$15:$C$18,3)

This takes the value in cell C3 (the percent used value for office 1) and searches the first column of the table defined by $A$15:$C$18 and returns the corresponding value in the 3rd column.  Thus a % equal t to 0 but less than 50% will returns 2, 50% to less than 70% returns 96, etc.    (note, $'s are used in the cell range reference $A$15:$C$18 to ensure an absolute reference when the cell is filled down for other rows.

To use the spreadsheet you should:

1. Give group names to all the objects (hatches) that will be referenced.
2. Edit the percent values as required.
3. Select the cells in column E (E3 down to as many rows as you have data).
4. Use Ctrl-C to copy the content to the Windows clipboard.
5. In AutoCAD use Ctrl-V to paste the statements after the Command prompt.

The hatching will change colors as specified.

The process could be more automated by having an AutoCAD script file (.scr) generated and then have AutoCAD run the script.

Here are the results.

Of course other rules can be used to define colors.