topic Re: remove formulas from tables in Visual LISP, AutoLISP and General Customization Forum

remove formulas from tables

robert06 — Tue, 07 May 2019 14:14:57 GMT

I'm looking for a way to automate removing formulas from autocad tables, replacing the contents with values.

A way would be exploding the tables upon export, but maybe there's a smarter solution?

Thanks!

Re: remove formulas from tables

pendean — Wed, 08 May 2019 15:56:54 GMT

Can you post an example DWG file with the table with formulas on one side and the desired result next to it at explains what the "value" being used is?

Re: remove formulas from tables

robert06 — Wed, 08 May 2019 19:43:01 GMT

I mean clearing the formulas in cells and replacing them with the results of the former formulas in the cells.

For instance a formula in Table 2 cell A1 is =Table1!A2 and it results "1" while the value of the cell in Table1 cell A2 is 1.

I'd like to transform the contents of Table 2 cell A1 to "1" to replace the formula with value.

Somewhat one would do in MS Excel when exporting a table and not willing to show the formulas but values.

The reason it would be needed is that referenced tables the values are linked from, are deleted during a file export procedure and we get # symbols in the cells.

Re: remove formulas from tables

CodeDing — Thu, 09 May 2019 21:58:53 GMT

@robert06 ,

As Pendean said, an example would be best so that we can help come to a solution. Without an example, your explanations can still be somewhat vague and everything provided to you will be guesswork.

Here's my shot at it:

(defun c:F2V ( / tbl r c rCnt cCnt)
;Formulas to Values
(vl-load-com)
;get table
(setq tbl nil)
(while (not tbl)
  (if (setq tbl (car (entsel "\nSelect Table: ")))
    (if (not (eq "ACAD_TABLE" (cdr (assoc 0 (entget tbl)))))
      (progn (prompt "...not a table.") (setq tbl nil))
      (prompt "...successful selection.")
    );if
    (prompt "...nothing selected.")
  );if
);while
;prep for loop
(setq r (getpropertyvalue tbl "NumRows"))
(setq cCnt 0 c (getpropertyvalue tbl "NumColumns"))
(setq tbl (vlax-ename->vla-object tbl))
;set cells to values without formulas
(repeat c
  (setq rCnt 0)
  (repeat r
    (vla-setcellvalue tbl rCnt cCnt (vla-getcellvalue tbl rCnt cCnt))
    (setq rCnt (1+ rCnt))
  );repeat r
  (setq cCnt (1+ cCnt))
);repeat c
;finish
(prompt "\n---F2V COMPLETE---")
(princ)
);defun

Best,

~DD

Re: remove formulas from tables

robert06 — Fri, 10 May 2019 07:00:49 GMT

Ok, sorry, it would had been probably more clear if i had mentioned converting the fields in cells to text, not just formulas in the fields. Attached a sample drawing now.

But, you got it right, the lisp does what was intended, thank you!

To go on, I'm not sure how to automate the selection (setq tbl (car (entsel "\nSelect Table: "))) to run for all tables in drawing

(if (setq ss (ssget "_X" '((0 . "ACAD_TABLE"))))
(progn
(setq sslen ((sslength ss))
(repeat (..

Re: remove formulas from tables

CodeDing — Fri, 10 May 2019 13:43:48 GMT

@robert06 ,

Thank you for the dwg. Here is a way to implement for all tables in dwg:

(defun c:F2V ( / ss tbl r c rCnt cCnt)
;Formulas to Values
(vl-load-com)
;get tables, exit if none
(setq ss nil)
(if (not (setq ss (ssget "_X" '((0 . "ACAD_TABLE")))))
  (progn (prompt "\nNo tables found...") (exit))
);if
;prep for loop
(setq len (sslength ss) cnt len)
;set cells to values without formulas
(repeat len
  (setq tbl (ssname ss (setq cnt (1- cnt))))
  (setq r (getpropertyvalue tbl "NumRows"))
  (setq cCnt 0 c (getpropertyvalue tbl "NumColumns"))
  (setq tbl (vlax-ename->vla-object tbl))
  (repeat c
    (setq rCnt 0)
    (repeat r
      (vla-setcellvalue tbl rCnt cCnt (vla-getcellvalue tbl rCnt cCnt))
      (setq rCnt (1+ rCnt))
    );repeat r
    (setq cCnt (1+ cCnt))
  );repeat c
);repeat len
;finish
(setq ss nil);i like to reset my selection sets
(prompt "\n---F2V COMPLETE---")
(princ)
);defun

Best,

~DD

Re: remove formulas from tables

robert06 — Mon, 13 May 2019 05:43:09 GMT

Just great, many thanks!

Re: remove formulas from tables

Anonymous — Wed, 18 Aug 2021 08:17:12 GMT

brother can you please help me out, how to use this above given code, to remove formulas from tables