How to reference table cell text in field?

[mstene]

I am trying to improve my workflow. In a drawing, I create a table datalinked to an excel sheet that we use to manage projects.

 

I then have blocks representing the items from the table. Currently, I edit these attributes by hand, but have recently been finding a lot of power in the fields.
Apparently, Field > Formula > Cell only allows numbers to show up in my attribute. How can I create a field that references the text of a table?

[CyberAngel]

From what I can tell, you need to get an add-on (third party) package or do some coding to solve this issue. But usually someone proves me wrong.

 

This same issue has shown up at least twice here at CADTutor (in 2010 and 2016). Plus someone who wanted to get a cell's handle.

 

To clarify, does the same block always reference the same cell? If not, how does AutoCAD know which cell to extract for a particular instance?

[mstene]

1 hour ago, CyberAngel said:

From what I can tell, you need to get an add-on (third party) package or do some coding to solve this issue. But usually someone proves me wrong.

 

This same issue has shown up at least twice here at CADTutor (in 2010 and 2016). Plus someone who wanted to get a cell's handle.

 

To clarify, does the same block always reference the same cell? If not, how does AutoCAD know which cell to extract for a particular instance?

 

 

Yes,

 

The idea is to set up a template, so that whatever data is linked into the table structure will get translated to the visual aspects of the block.

So that if a customer wants to do a different configuration order, swapping them in the excel sheet, then reloading the data, will update the visual aspects.

I can probably get away with getting a 3rd party component, but I'll fall apart when it comes to the coding part. I have an ok grasp of BASIC and bits of Python, but trying to dissect Diesel and Lisp is painful. I did see the 2016 post, and lost a little hope, but hopefully 8 years is enough time to try again.

 

 

[BIGAL]

Maybe go the other way, update the block attribute from excel, then use field in cell. You would need to save the handle ID in Excel of the block or maybe better the attribute. So does your Excel have the handle saved? 

 

A question how are you making the table now ? Ps no probs Acad <--> Excel for me.

 

I think we need to see a couple of lines of your excel.

[mstene]

On 1/17/2024 at 9:06 PM, BIGAL said:

Maybe go the other way, update the block attribute from excel, then use field in cell. You would need to save the handle ID in Excel of the block or maybe better the attribute. So does your Excel have the handle saved? 

 

A question how are you making the table now ? Ps no probs Acad <--> Excel for me.

 

I think we need to see a couple of lines of your excel.

 

 

If I can go directly from the excel sheet into a block's attribute, that'd be even easier. That's where I started trying to do this, but couldn't find the solution. I already knew the pathway to linkdata, so I figured that had to hold the easiest onramp into the solution. I'm not sure what you mean by the Handle ID in excel. Could you elaborate this? I think that's going to be what I'm trying to do.

The excel sheet is nothing special. It's just listing out a pointlist, and then a range of cells carrying signal scaling and terminations. It's largely text, with a few numbers. Then inevitably points and instruments get changed before the project is built. So if we can update the data in excel, then refresh those fields in CAD it'll save me some revision time throughout the life of our projects. 

 

 

[BIGAL]

Use this and it will return the Handle of any object it is contained within the dwg and never changes. 

 

(setq hand (cdr (assoc 5 (entget (car (entsel "\nPick any object"))))))

 

You may want this also.

Alan Excel library.lsp

[mstene]

On 1/19/2024 at 9:06 PM, BIGAL said:

Use this and it will return the Handle of any object it is contained within the dwg and never changes. 

 

(setq hand (cdr (assoc 5 (entget (car (entsel "\nPick any object"))))))

 

You may want this also.

Alan Excel library.lsp 14.75 kB · 13 downloads

 

Forgive me,

 

Would you be able to give me a brief rundown on how to use that command? I have installed the lisp you attached, but I don't know how to actually utilize what you've provided.

 

[BIGAL]

The Alan excel has multiple defuns that you can use in a custom lisp to carry out your task. You start with say get Excel that is open, then getcells so read all the values into a List inside say Autocad. You need some way to link the Excel cell value to a particular block. Or do you pick a block then pick a excel cell ? 

 

Can you post a dwg and a excel ?

[Danielm103]

On 1/17/2024 at 6:09 AM, mstene said:

How can I create a field that references the text of a table?

 

 

this might be of interest https://www.theswamp.org/index.php?topic=59145.msg618669#msg618669

^ I intend to publish this as open source

 

edit, as a shameless plug, here's a Excel field evaluator, the free version doesn't expire and will let you evaluate like 30 fields.

https://apps.autodesk.com/ACD/en/Detail/Index?id=4446191126131749248&appLang=en&os=Win64

Edited January 30 by Danielm103

[mstene]

On 1/30/2024 at 2:51 AM, Danielm103 said:

 

this might be of interest https://www.theswamp.org/index.php?topic=59145.msg618669#msg618669

^ I intend to publish this as open source

 

edit, as a shameless plug, here's a Excel field evaluator, the free version doesn't expire and will let you evaluate like 30 fields.

https://apps.autodesk.com/ACD/en/Detail/Index?id=4446191126131749248&appLang=en&os=Win64

 

your shameless self-plug is certainly the closest I've been able to get so far. It's slightly more legwork than I had dreamt up, BUT it can absolutely be templatized in my workflow. Expect a full-version purchase soon.

 

 

[mstene]

On 1/29/2024 at 7:11 PM, BIGAL said:

The Alan excel has multiple defuns that you can use in a custom lisp to carry out your task. You start with say get Excel that is open, then getcells so read all the values into a List inside say Autocad. You need some way to link the Excel cell value to a particular block. Or do you pick a block then pick a excel cell ? 

 

Can you post a dwg and a excel ?

 

 

I'm sorry, we're clearly on different ends of Autocad expertise, and I don't think I'm going to be at the level you're writing to anytime soon. I have the library, now though, so I may manage to work my up to that level of understanding - but for the time being, I am going to use the 3rd party software linked from DanielM to accomplish this task. I appreciate your insight, input, and patience.

[BIGAL]

The issue is to work out a user sequence, I think you will need at least 2 functions, the 1st opens Excel then you pick a cell pick a block the attribute is updated, likewise the Handle ID is added to your excel, a table is made and the attribute value added as a field into the table. The second function is change the values in Excel and update the dwg all done with minimal user input. 

 

So in finalising need that critical link of identify which object to update.

 

To do something need a real dwg with blocks, a table and the matching Excel. Only need like 5 rows.

 

Edited January 31 by BIGAL