Jump to content

Change Color by Excel Data?


Recommended Posts

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.


Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.



Link to comment
Share on other sites

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.



Link to comment
Share on other sites

@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? 


Link to comment
Share on other sites

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 by lrm
Removed references to groups.
Link to comment
Share on other sites

 "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 by BIGAL
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...