Change Color by Excel Data?

[Saltwatetaffy]

Does anybody know if it's  possible to change the color of a block by excel data? I'm currently working on a heatmap so i can 

see the difference between good and bad slotmachines on a floorplan.

 

[lrm]

It's not clear what constraints you have with the blocks but here is one scheme that may works for you.

Objects on layer 0 of a block will take on the properties of the layer in which the block is located.  Objects in a block that are NOT on layer 0 will remain on their layer of creation.  With this in mind you can use the change command to change the layer of the block to a layer with the default color you want.

 

For example, the contents of cell C2 in following Excel worksheet contains:

=CONCATENATE("change"," ","g"," ",A2," ",CHAR(12)," ","p"," ","la"," ",VLOOKUP(B2,$A$11:$B$13,2)," ")

 

 

 

It uses the Excel vlookup function to set the color to layer correspondence.  Each block is assign a group name that is used to select the block so that its layer can be changed.

Copy and save the contents of column C2 down as a text file with the extension .scr.  Run the script in AutoCAD to change the block colors.

An  test file is attached.  Four blocks are assigned groups names "g1 to "g4) (from left to right).  Before the script is run the files looks like:

After running the script the file looks like:

The yellow circles are another layer and are not affected by the script.  The script generated for this example is:

 

 

block color test.zip

[BIGAL]

Like Lrm you can read an Excel even not open, and get a range of cells or maybe in this case "All" cells. Then as suggested change layer for color change.

 

Read "All" is a function of getexcel.lsp. 

 

If you can post a sample dwg and Excel then can maybe put something together.

 

 

[Saltwatetaffy]

First of all many thanks to lrm for the reply!! I've tried the change command and it works great! question : Do i have to group every block? There about 

600 blocks(slotmachines) on the floorplan. 

I'm now using autocad for about three years so i dont have much experience in programming.

 

 

[lrm]

@Saltwatetaffy you asked "Do i have to group every block? ".  Since you want to be able to control the color of each block independently from all the other blocks you need a method to select each block and then associate it (via Excel) with a color.  I choose to use group names to do the selection.   I assumed that there may be several instances of the same block and therefore could not use block name as the identifier.  If you knew the location of each block you could use select at a point to individually identify each block.  The X,Y location of the block would need to be included in the Excel worksheet and the command (column C) modified to use it instead of a group name.  Do the locations of the blocks remain constant? 

 

[lrm]

I've modified the Excel worksheet to work with block location instead of groups.  Cell D2 contains:

=CONCATENATE("change"," ",B2,",",C2," ",CHAR(12)," ","p"," ","la"," ",VLOOKUP('Block data'!A2,'Lookup Table'!$A$3:$B$5,2)," ")

I moved the lookup table to another sheet.

 

 

 

block color test v3.zip

 

 

Edited November 3, 2022 by lrm
Removed references to groups.

[BIGAL]

 "X,Y location of the block would need to be included"

 

Simpler is save "Handle" then if block moved will still work. Hint (handent "11C4C")

Edited November 3, 2022 by BIGAL