General Autocad to Excel (adding cell formulas)

[rcb007]

With the below code as an example. I am wondering how I could add cell formulas into the excel file it creates. Unless I am not seeing where I could modify that from.

 

If i could put in the below code as a simple example. (add C5 + C6 in C7). I know we i could give more complex forumulas if I can figure the basics out.

 

(defun c:Blocks2Excel (/ bks int sel ent nm ds b ly f lst fl op)
(vl-load-com)
;;https://forums.autodesk.com/t5/visual-lisp-autolisp-and-general/lisp-to-explode-all-dynamic-blocks-except-named-blocks/td-p/7700420
 ;;--------------------------------------------;;
 ;;						;;
 ;; Author : Tharwat 10.June.2015		;;
 ;; Modified on 3rd.Aug.2017 to allow users to	;;
 ;; select specific blocks.			;;
 ;;--------------------------------------------;;
 ;; Compute the quantity of all blocks in	;;
 ;; a drawing and write the outcome to an	;;
 ;; Excel file format in format .csv		;;
 ;;--------------------------------------------;;
 ;; Layer Name:,Block Name:,QTY,Description	;;
 ;;						;;
 ;;--------------------------------------------;;
 (princ"\nSelect blocks :")
 (if (setq bks (vla-get-blocks (vla-get-ActiveDocument (vlax-get-acad-object)))
           int -1
           sel (ssget '((0 . "INSERT"))))
   (while (setq ent (ssname sel (setq int (1+ int))))
     (if (and (setq nm (vla-get-effectivename (vlax-ename->vla-object ent)))
              (setq ds (if (vlax-property-available-p (setq b (vla-item bks nm)) 'comments)
                         (vla-get-comments b)
                         "")
                    )
              (setq ly (cdr (assoc 8 (entget ent))))
              )
       (if (vl-some '(lambda (x) (and (eq ly (car x)) (eq nm (cadr x)) (setq f x))) lst)
         (setq lst (subst (list ly nm (1+ (caddr f)) ds) f lst))
         (setq lst (cons (list ly nm 1 ds) lst))
         )
       )
     )
   )      
 (setq lst (vl-sort lst '(lambda (j k) (< (car j) (car k)))))
 (cond ((not lst) (alert "Couldn't find any block in this drawing !!"))
       ((and (setq fl (getfiled "Specify new Excel file name" (getvar 'DWGPREFIX) "csv" 1))
             (setq op (open fl "w"))
             )
;;        (write-line "Layer Name:;Block Name:;Quantities;Description" op)
        (write-line "Block Name:Quantities" op)
        (mapcar '(lambda (x)
;;                   (write-line (strcat (car x) ";" (cadr x) ";" (itoa (caddr x)) ";" (nth 3 x)) op))
                   (write-line (strcat (cadr x) ";" (itoa (caddr x))) op))
                lst)
        (close op)
        )
       )
 (princ)
 )

(c:Blocks2Excel)

 

 

Thank you for any help.

[BIGAL]

Ok had a play and you can just pass a value into a cell using a direct method of Autocad/Bricscad talking to excel. No Csv.

 

So I put 10 in A1 20 in B1 then did (putcell "C1 "=A1+B1") the result as required.

 

 

If you get a copy of getexcel.lsp it has a putcell function. I used my own version of a excel.lsp program.

 

This is the very cut down version but worked. The full code does much more options like pick a excel file etc.

 

(defun putcell (cellname val1 / )
(setq myRange (vlax-get-property  (vlax-get-property myxl "ActiveSheet") "Range" cellname))
(vlax-put-property myRange 'Value2 val1)
)
; have excel already open
(or (setq myxl (vlax-get-object "Excel.Application"))
    (setq myxl (vlax-get-or-create-object "excel.Application"))
)
(vla-put-visible myXL :vlax-true)
(vlax-put-property myxl 'ScreenUpdating :vlax-true)
(vlax-put-property myXL 'DisplayAlerts :vlax-true)

; putcell example
(putcell "C1" "=A1+B1")

 

[mhupp]

My 0.05¢ if they are wanting to add formulas it might not be as simple A+B.

(defun putformula (cellname val1 / )
  (setq myRange (vlax-get-property  (vlax-get-property myxl "ActiveSheet") "Range" cellname))
  (vlax-put-property myRange 'Formula2 val1)
)

 

this VBA code was giving me an error with a long formula

cell.Offset(0, 1).Value = "=IFERROR(INDEX(list!$B$2.....
it was inputting an @ infont of index for some reason.
=IFERROR(@INDEX(list!$B$2.... 
Changing value to formula2 fixed this issue
cell.Offset(0, 1).Forumula2 = "=IFERROR(INDEX(list!$B$2:$B$...

Edited December 17, 2022 by mhupp

[BIGAL]

Good idea will add to my library the hint.