Community
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.
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.
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.
Forums Links
Can't find what you're looking for? Ask the community or share your knowledge.
Post to forums
