Change block layer based on spreadsheet information

[Anonymous]

I’m currently using AutoCad 2021. I am a novice user using AutoCad for reviewing/editing engineering drawings and for rudimentary sketches when other presentation platforms aren’t precise enough for my purposes. That being said, I am supporting the mobile food pantry program for our local foodbank by managing COVID safe delivery of two USDA food programs at several senior living complexes in our area. To do this, I use excel to pre-register residents, and indicate if they receive one or both food programs based on entries in column A (CSFP program) and column B (TEFAP program). Columns A and B are conditionally formatted to change column A entries to green when selected and column B entries to pink when selected. I have drawn maps of each apartment complex, in which I highlight each apartment on the printed map by hand with pink and/or green to match the excel file. What I would like to do is update the map using the excel file to automatically color the apartments based on the excel file data. I have seen several similar inquiries for hatching, filling objects based on excel data but none quite do what I want. I’d like to redraw the map so that the apartment and apartment numbers are on a layer, blocks containing the pink fill color is on a layer, the green fill color is on another layer, and frozen layer to turn fills off. I’d like to generate commands in excel to change the layer of the block fill to the green layer or the pink layer or to the frozen layer, then paste the commands into the drawing to make the map colors match the excel file. I’m happy to paste the commands in manually, but would like to explore the use a script file to automate if I can make this work manually. This seemed the best way to me since there are approximately 150 apartments at each complex and I haven’t found a good way to do this controlling the colors of individual blocks with attributes using excel data. Is there a better way to do what I’m trying to do? I’ve seen similar posts on other forums but none have answered the query. I have attached a sample excel file and sample maps to illustrate what I’m trying to do.

[fatal.error · Accepted solution]

Please follow these steps:

1. Create a new file with the following layers: "Hatch TEFAP", "Hatch CSFP" and "Hatch UNDEFINED". Set its layers color as desired.

2. Create a block named "ROOM" containing "PROGRAM" and "ROOM_NUMBER" attributes. You can add some dynamic parameters and actions to control size.

3. Populate the model space with all the existing rooms and set its ROOM_NUMBER values.

4. Open the Express Tools ribbon's tab and click on Export Attributes.

5. Set location and name for the .txt file, click Save, select all the existing rooms and hit Enter.

6. A new txt tab-delimited file will be created.

7. Edit the file either on Notepad or Excel. In the latter case, open Excel, create a new file, on the Ribbon select the Data Tab, click From Text, select the .txt file, on Original Data Type make sure to select Delimited, click Finish and finally OK.

8. Edit all the imported cells content as desired.

9. Once finished, click on Save As, on Save as Type select Text (tab delimited), click Save and finally Yes.

10. Return to AutoCAD. Open the Express Tools ribbon's tab, click on Import Attributes, select the edited txt file and click Import. All the existing block's attributes will be updated.

11. To match all the updated attributes with it's corresponding layer, open Notepad and create a new .lsp file like the following:

(defun c:MATCHALL (/ SSBlks en an ad avs nlyn)
(if (setq SSBlks (ssget "x"'((0 . "INSERT")(66 . 1))))
(repeat (sslength SSBlks)
	 (setq en (ssname SSBlks 0)
	       an (entnext en)
	       ad (entget an)
	 )
	(while (= "ATTRIB" (cdr (assoc 0 ad)))
	  	(if (= (cdr (assoc 2 ad)) "PROGRAM")
		  	(progn
			  	(setq avs (cdr (assoc 1 ad)))
		  		(if (setq nlyn
				(cond  ; for a New Layer Name
    			        ((wcmatch  avs "*TEFAP*")  "Hatch TEFAP"     )  
			            ((wcmatch  avs "*CSFP*")  "Hatch CSFP" )  
			            ((wcmatch  avs "*UNDEFINED*"  )  "Hatch UNDEFINED"       )))
				(entmod (subst (cons 8 nlyn ) (assoc 8 (entget en)) (entget en)))
				(progn (princ"\n No match of old value: ") (princ avs))
				  )
			)
		  )
			(setq an (entnext an)
                    		ad (entget an))
		  )
  (setq SSBlks (ssdel (ssname SSBlks 0) SSBlks))
		)
  (princ"\n No Blocks with Atts found "))
  (princ"\nDone. ")(princ)
  )

12. Save the lisp, drag-and-drop it into AutoCAD, click Always Load and type MATCHALL on the command line.

13. All blocks will be changed to its appropiate layer. Furthermore, you can set the LISP to run automatically at startup. Please see all the attached files.

[Anonymous · Accepted solution]

Your thorough reply is much appreciated. Those are exactly the details I needed.

[Anonymous]

After reviewing your response to my query, I realized the solution provided may not do exactly what I was hoping for. If I understand correctly, the steps will allow me to change the block color to the color of the TEFAP layer, or the CSFP layer, or the undefined layer. This is almost what I want to do. However, as shown in the attached file, I also need to be able to make both fill colors show up in a room if both programs are selected on the excel file. This means I need to format a fill color for TEFAP program to fill half the apartment outline, and the fill color for the CSFP program to fill the other half of the apartment outline. This would indicate that the resident of the apartment may get one food program or the other or both. What I see needing to be done is having a layer showing the apartment outlines, and then making TEFAP blocks, and CSFP blocks, each half the size of the apartment and tied to the associated apartment number using block attributes. Am I asking to do something too convoluted or can your solution be adjusted to accomplish this?

[fatal.error]

As you said, seems a little bit convoluted. A cleaner and cost-effective approach could be creating a new layer "Hatch BOTH", adding the following line to the lisp:

			            ((wcmatch  avs "*BOTH*")  "Hatch BOTH" )
			            ((wcmatch  avs "*SOMETHING*")  "Hatch SOMETHING" )  

Furthermore, if all low-detail rooms in layouts are perfectly squared, how about doing all the work just in Excel? Conditional Formatting and SUMIF() formulas might help a lot.

[Anonymous]

I'll consider that option. I'm hoping to flush out a workable solution on a typical uncomplicated single building as was reflected in my previous attachment. If I can flush out a workable solution, I want to apply it to our more larger complexes that we serve that upwards of 150 buildings, each with 8-12 apartments, spread over a 40 acre area. These buildings vary in size and configuration. At these locations, we are distributing between 12,000 to 14,000 lbs of food to approximately 140 residents. The excel file and maps allow to re-palletize the bulk delivery of food to multiple locations in the complex to make delivery easier. Your replies have given me some interesting options to try, so I'll let you know if I develop something workable for me.

[Anonymous]

Since my last reply, I sketched up a test drawing to see if I could manually control the fills of the apartments as the basis for controlling fills using the excel file by linking the file to the drawing. First, I laid out a building/apartments outline on a layer called Apts. I put apartment numbers on a layer called Apt No. Fig 1

Fig 1

Then I created a TEFAP layer (pink), a CSFP layer (green), and an Undefined layer (white). I added three objects (circles), each on one of the three layers. I added a solid fill for each apartment on the TEFAP layer and a solid fill for each apartment on the CSFP layer. Each fill is half of the boundary of an apartment. Fig 2

Fig 2

I used matchprop to change the layer of all the fills to the undefined layer, using the circle on the undefine layer as my reference object. Fig 3

Fig 3

Referencing the excel file, I changed the fill layers of the hatches to match the TEFAP and CSFP selection using matchprop, with the circles on the TEFAP and CSFP layers as the reference objects. Fig 4

Fig 4

Next, I froze the Undefined layer to so that only the TEFAP and CSFP fills were visible. Fig 5

Fig 5

When printed, I get the attached dwg, which is how I’d like to populate the fills on my maps to match the excel file data.

Obviously, I have not used any blocks or attributes at this time since I wanted to make sure I could manually achieve the desired effect. Now that I have manually achieved the desired effect, I’ll look to adapt the previous responses to link the excel file to the drawing to control the fills. Any recommendations towards this end is appreciated.

[Anonymous]

Follow up to my last reply.

After demonstrating that I could manually control the two fill colors for each apartment, I reviewed several AutoCAD forum threads that provided guidance on ways to do this, but I leveraged heavily off of leeminardi’s 05-15-2019 response in the “Change hatch color based on speadsheet information” forum thread.

He described using the CONCATENATE function in the excel spreadsheet to generate AutoCAD commands data that could be pasted into AutoCAD. CONCAT is the updated command that replaces the CONCATENATE function, but the CONCATENATE function still works and that is what I used for this effort. leeminardi provides a good explanation of how the CONCATENATE function works and how to format it, so I won’t repeat it here.

As described in my previous post, two discrete fills are generated for each apartment, each fill located on the appropriate program layer, TEFAP or CSFP. (See previous posts for an explanation of the program layers.)

Each fill is assigned a unique group name based on its program layer and the apartment number. This allows the fill to be individually selected by group name. To illustrate for apartment 201, the green fill is assigned a group name C201 and the pink fill is assigned a group name T201. This is repeated for every apartment.

Once group names are assigned to the fills, columns G, H, I, and J are added to the excel file to generate the appropriate group names and layer assignments based on the food program selected for each apartment.

The CONCATENATE function is used to add a C or a T to each apartment number to generate Group Names in columns G and H as follows.

=CONCATENATE("C",D3)  and  =CONCATENATE("T",D3)

For the layer assignments, a logical function was used to assign the fill to the program layer (CSFP or TEFAP) or to an undefined layer (UND) based on the excel data in columns B and C:

=IF(B3<1,"UND","CSFP")   and    =IF(C3<1,"UND","TEFAP")  

Using the Group Names and layer assignments determined above, the CONCATENATE function is used to generate AutoCAD commands that can be copied and pasted into the drawing command line to control the layer assignments of the fills. For the CSFP commands, the CONCATENATE function is as follows:

=CONCATENATE("(COMMAND ",CHAR(34),"chprop",CHAR(34)," ",CHAR(34),"g",CHAR(34)," ",CHAR(34),G3,CHAR(34)," ",CHAR(34),CHAR(34)," ",CHAR(34),"la",CHAR(34)," ",CHAR(34),I3,CHAR(34)," ",CHAR(34),CHAR(34),")")

This produces the following output:

(COMMAND “chprop” “g” “C201” “” “”la” “UND” “”)

which tells AutoCAD to activate the chprop command, selects group name C201, and changes its layer to UND. This is done for each row and for both programs.

The cells in each AutoCAD command column are selected (down to as many rows in the column) and using Ctrl-C, copied to the Windows clipboard.

Ctrl-V is used to paste the commands into the command line of the drawing. Do this for each of the two programs. The fills will change color based on the layer assigned. Freeze the UND layer to hide the fills on the UND layer. This will generate the fill colors for each apartment based on the excel data.

As leeminardi stated in his post, this could be automated further with a script file or Lisp routine. For me, copy and paste is good enough.

The implementation above used the hatch command to generate basic fills with unique group names assigned to each fill. It’s not easy to know which group name is assigned to a specific fill when you have a lot of fills to manage. I repeated the effort above using filled blocks instead of basic hatches. An attribute using the group name of the fill is added. Although the block is selected using the group name, the attribute allows the assigned group name to be viewed on the drawing. The commands copied and pasted from excel control the block layers in the same manner as the hatches in the original implementation. Since the title of this post was “Change block layer based on spreadsheet information, this answers the mail for my purposes.

[JuJitsoup]

@fatal.error

This is what I'm looking to do with a series of site plans, but also to add-in some automation via a link to an Excel doc.

Each site plan contains a number of the same block (named "Lodge") placed at various locations and orientations. Each one represents a camping lodge and its location on the site. There are 10 lodge types, and each Lodge type has a separate layer and colour. The blocks are placed on the corresponding Lodge type layer for easy visual identification via the layer colours.

Each block has the attribute  "Pitch_Number" and each one is edited to identify its unique pitch, noted as "Pitch 01", "Pitch 02" etc.

The lodge types change often and this info is noted on an Excel spreadsheet (edited by someone else). I then use this Excel doc as a guide to identify the pitch numbers that have changed type and manually move (change) that Lodge block to the correct Lodge type layer.

There are many sites and it iterally takes weeks to do this, and is just painful, and also prone to human error as I get tired and very bored.

Ideally, the Excel spreadsheet would automatically adjust/move the corresponding Lodge blocks to their new Lodge Type layer when changes were made in Excel, and thus removes the need for input from me, other than to print the plans out after an update.

Your method is excellent and will definitely help, but is there a way to fully automate the process as I've described?