Jump to content

Update Block layer with Excel input


Recommended Posts

Hi all,



I'm currently using AutoCAD 2014. I'm use to using AutoCAD but have never used VBA or AutoLISP before. Really just found out about them from doing some research on the internet and hoping for some help with my dilemma. I have a floor plan already drawn and each individual room already has a separate hatch created as a block with a unique identifier. What I would like to be able to do is somehow make the layer of the block 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 block title like Kitchen, Bath Room 1, Living Room, etc. and column B would contain the layer I want to move it to. So when I change column B from 20 to 30 the layer updates automatically. A couple different people will be inputting to the spreadsheet which is why I want it to be able to the update automatically when needed. My floor plan contains about 60 different spaces (blocks) and has about 10 different colors (layers) to represent the percent completes. I'm not really sure how to go about this but I'm very much willing to learn. Eventually the drawing will grow to have over 300 individual blocks and around 12 layers. So you can see the amount of effort it would save versus selecting each block and changing it's layer individually.





Link to comment
Share on other sites

What you want to do is very doable, there is heaps of posts about to and from excel the problem is finding one that does similar to what you want. I would start searching using "Title block & excel" there is a lot about updating sheet details from excel. The one thing though you would need column A block name = bedroom column B room number =32, column c layer % as you want.


There may be smarter ways to do it rather than have each room with a unique block that appears only once. Like find room number change hatch pattern color, can be via find room number its on top of the hatch so change. ie insertion pt for find hatch at this point.


As you say limited lisp skill so you will need help, try to find something then come back, also a good idea is post a dwg with say a few rooms so others can see the way you have made the hatch block.


An rough example of changing a hatch colour

; draw a sq and hatch it
; put a text on it say a number make sure inside hatch
(princ "\nPick plain text on a hatch")
(setq obj (entsel)) 
(setq pt10 (assoc 10 (entget (car obj))))
(setq pt (list (cadr pt10)(caddr pt10)))
(command "Change" pt "" "P" C 120) ; should now be color 120

Link to comment
Share on other sites

I found this code from Fixo that looks like it has some of what I need. I just don't how to modify the code to do what I need to do. I get the part where I tell it what drawing to open. Then I tell it the cell range to chose the block name from. But I need it to then change the block layer based off of column B in the spreadsheet not set the attribute text to structure. Any input would be greatly appreciated. http://www.cadtutor.net/forum/showthread.php?66073-Link-Excel-VBA-to-acad&p=589149#post589149



Link to comment
Share on other sites

I have attached the excel file, but can't post the actual drawing file for company reasons they won't let me. The excel file is named House 3 Progress and is located at C:\Documents\House 3 Progress.xlsx. The AutoCAD file is name House 3 and is located at C:\Working Drawings\House 3.dwg All the hatched areas are made into blocks that are labeled just like Column A of the spreadsheet and the layers are created already to represent Column B.





House 3 Progress.xlsx

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...