Jump to content

I know this has been asked a thousand times....


Jim Clayton

Recommended Posts

BOM Block Attributes. Does anyone have a straight to the point answer on how to change them with Excel information. I've read roughly 10,000 articles and forum posts. My pc is drowning in downloaded Lisps that aren't doing the trick. ATTIN/OUT is too many steps for my intended purpose. Is there a Lisp, some VBA, a magic wand, a golden goose, anything out there, that will get the data from my Excel sheet onto the BOM Block Attributes of my drawing in roughly 3 steps or less. I'm willing to offer 1 free emoji and a crisp hi five to any would be solvers of my dilemma. Please and thank you.

Link to comment
Share on other sites

  • Replies 26
  • Created
  • Last Reply

Top Posters In This Topic

  • Jim Clayton

    13

  • BIGAL

    5

  • rlx

    5

  • guitarguy1685

    2

Top Posters In This Topic

Posted Images

The answer is out there and you are correct getting one that does exactly what you want is hard, say 95% are hard coded take this block attribute named X, put in cell x y or the opposite get a cell and put in block. One method of getting around this is to use the block attribute order rather than tag name, again though your blocks have different names and number of attributes 1,2, 20 etc

 

3 steps

pick block

pick excel sheet row-col

change attributes

 

ok now for the hard facts hidden behind step 1 is a data file with all the correct block settings and the column settings of the correct type of excel spread sheet.

 

It may be something that may cost more than a emoji as this type of request opens that can of worms, Can I now have option 3 ? Can I now have option 25 ?

 

Post a dwg and a excel you never know, be very clear about what is going where.

Link to comment
Share on other sites

BigAl, Thanks for taking the time to respond. Okay so I attached a copy of the dwg with the Block that I'm trying to modify. The Spreadsheet, that's another story. I have it, but it's an XLSM file and I wasn't able to upload it. The best I could provide was a screenshot, unless someone can let me know how to upload it. Basically I built a platform in Excel to sort through information super quick, narrow it down to one line of info, and add that line to your BOM. The goal is to then TRANSFER that BOM into AutoCAD. There lies the brick wall. And if anyone can help me get this working I'll gladly post a link or something to the entire thing for all to use as I'm certain I'm not the only one who uses BOM's everyday.

SAMPLE.dwg

BOM.pdf

Link to comment
Share on other sites

Ok. Deleted everything but the first sheet and compressed the file and was able to attach it. The Buttons won't work but it's better than a screen shot. Tks.

BuildMyBOM.zip

Link to comment
Share on other sites

I have tried Paste Special/AutoCAD Entities, and it produces the results above...which yes, technically gets the information from Excel into AutoCAD, but my goal is to modify the entities. In short, because the boss man won't allow it. But I appreciate the suggestion. Tks.

Link to comment
Share on other sites

A table would be easier in terms of pasting, but the blocks have been a company standard since long before I came along. I'm trying to expedite the process of filling them out via an Excel database. I've come across lots of Lisps and a few snippets of VBA that were close, but nothing that has been able to do the trick. I know it's possible. It's just beyond my capabilities. ATTIN/OUT works, but is too many steps because multiple people will be using this dozens of times a day. I'm trying to find a Lisp or some VBA or something that can populate the attributes with the information in Excel, from either end. Thanks for your help.

Link to comment
Share on other sites

Maybe I don't understand the issue, but have you tried linking an excel range to a table in autocad? This seems to simple a solution so I probably missed something.

Link to comment
Share on other sites

A table would be a simple solution, but the BOM's in our Title Blocks our comprised of Blocks that look like the attached. They have attributes that we currently modify manually. I'm trying to improve this process with a super fancy shared Excel Workbook, where everyone can sort through all the parts they need at the speed of...faster, then upload that information into the current blocks (attached). When I say "a table will not work", I don't mean in the technical sense, I mean in the "boss won't allow it" sense. I have to use the blocks and update the attributes. Which brings me back to the beginning. How can I update the attributes with the Excel data? Thanks for your help.

BOM-Model.pdf

Link to comment
Share on other sites

Oh I see. Too bad the boss doesn't see the utility of it. I used to work at a curtain wall company and had an extensive BoM excel sheet for fasteners. This was linked to tables on our sheets. But I also had free reign to do what I wanted.

Link to comment
Share on other sites

Yeah, it is what it is...think our company standards our based on practices overseas. Trying make the most of it though and make things a little less tedious with the tools in front of me. Unfortunately there's not an "out of the box" solution for any of this, at least to my knowledge. I've tried just about every Lisp routine I've come across but nothing is doing the trick...everything almost works. Very frustrating.

Link to comment
Share on other sites

I use Getexcel.lsp and one function is get a range of cells and make a lisp list this would be all the relevant rows, then just a case of pick start point and keep repeating the insert a block filling in attributes. That is the easy part.

 

; GetExcel - Stores the values from an Excel spreadsheet into *ExcelData@ list
;   Syntax:  (GetExcel ExcelFile$ SheetName$ MaxRange$)
;   Example: (GetExcel "C:\\Folder\\Filename.xls" "Sheet1" "L30")

 

I just have to find some time.

Link to comment
Share on other sites

RLX-About to test out the above Lisp. Will keep you posted. And thanks for taking the time to help.

 

 

BigAl- I've tried using the GetExcel Lisp and I'm able to "grab" the cell ranges, but then nothing happens. It just grabs them and displays them in the command line. I don't know how to paste them anywhere. I'm certain it's user error, but just as a general curiosity I'm wondering what it is that I'm doing wrong...primarily because I've been at this for as long as I have. Thanks.

Link to comment
Share on other sites

RLX-You magnificent genius! This is awesome. There's some minor tweaking that I would need in order to use it still so if anyone could help with that I would greatly appreciate it, but overall it's doing the job as it should.

 

 

Tweaking: For "Item Number" and "Stock Code" these numbers are being brought in as Numbers but would need to be brought in as Values.

Example: Number=1.00, Number=55983835.00

Value=1, Value=55983835

 

 

Also, I can't find the command to launch this. I've only been able to launch it once by loading the Lisp, then I have to close the drawing out and start over.

 

 

Lastly, it pastes one row at a time fine. If I try to paste 3 rows, it's not pasting the second row. And this could very well be user error that could be resolved with some direction.

 

 

Again, this is great. Thanks so much for your help. This gives me something as a base point.

Link to comment
Share on other sites

RLX-You magnificent genius! This is awesome. There's some minor tweaking that I would need in order to use it still so if anyone could help with that I would greatly appreciate it, but overall it's doing the job as it should.

 

 

Tweaking: For "Item Number" and "Stock Code" these numbers are being brought in as Numbers but would need to be brought in as Values.

Example: Number=1.00, Number=55983835.00

Value=1, Value=55983835

 

 

Also, I can't find the command to launch this. I've only been able to launch it once by loading the Lisp, then I have to close the drawing out and start over.

 

 

Lastly, it pastes one row at a time fine. If I try to paste 3 rows, it's not pasting the second row. And this could very well be user error that could be resolved with some direction.

 

 

Again, this is great. Thanks so much for your help. This gives me something as a base point.

 

 

I have pasted it from my vt.lsp so I didn't change to much to avoid the risk it stoppend working. But just rename the first defun '(defun VT_PasteFromExcel' and rename it to something like '(defun c:JimWillFixIt or whatever. And delete the last line in the lisp file (VT_PasteFromExcel). I usualy use this to auto start the routine when testing. Now you can start your routine at the command prompt with just JimWillFixIt or (c:JimWillFixIt).

 

 

Halfway the file you find a line '; ********* change to (rtos val 2 0) -> no decimals , (rtos val 2 2) = 2 decimals. Just change in the NEXT line (rtos val 2 2) to (rtos val 2 0)

 

 

Haven't had the problem you mention with skipping a row. Maybe turn osnap off?

 

 

 

gr. Rlx

Link to comment
Share on other sites

Precision has been set, command has been assigned, still can't figure out what's going on with the pasting of the rows though. It's weird because if I try to paste three rows, it skips the row 2, but if I try to paste two rows, it only gives me the first one.. This whole thing is amazing by the way. This is exactly what I wanted to do. Can't thank you enough.

Link to comment
Share on other sites

Ok I got it to paste all three rows, but the first time they were stacked one on top of the other, and the second time they pasted perfect. So now we have 3 variations.

Link to comment
Share on other sites

Ok I got it to paste all three rows, but the first time they were stacked one on top of the other, and the second time they pasted perfect. So now we have 3 variations.

 

 

have checked you objectsnap setting , else add (setvar 'osmode 0)

 

 

gr. Rlx

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.

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