Update Block layer with Excel input

[jmb789]

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.

 

Thanks,

 

JMB

[BIGAL]

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

[BIGAL]

A starting point for you search for "Getexcel.lsp"

 

also http://www.cadtutor.net/forum/showthread.php?66073-Link-Excel-VBA-to-acad&highlight=Title%2Bexcel

[jmb789]

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

 

Thanks!

[BIGAL]

Need two things your excel and a your dwg just with a few blocks. We can guess stuff but makes it easier if we know block names etc.

[jmb789]

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.

 

Thanks!

 

JMB

House 3 Progress.xlsx