topic Re: AutoLisp to pass block attribute ID name and block count of a specific row to “Putcell” command for the GetExcel routine in Visual LISP, AutoLISP and General Customization Forum

AutoLisp to pass block attribute ID name and block count of a specific row to “Putcell” command for the GetExcel routine

rebPTFNF — Sat, 14 Aug 2021 21:28:06 GMT

I’m creating a pricing sheet by pulling attribute IDs and their duplicate block count (block instances) from a CAD drawing and putting them into an existing excel file. I think I have most of the separate parts needed but can’t figure out how to put it all together so looking for some help.

These are the Steps I believe are needed in my routine:

Load the GetExcel.lsp functions needed to put info from CAD into existing Excel.
Open Excel to write.
Get attribute ID name from a block. Associate name with a row. EX: ID name “A006” = row 10.
Get duplicate count of that block. Ex: “3”.
Create a loop (foreach?) to add ID name and count into corresponding “Putcell” commands (know by its row number). I can have a putcell command with a row for all 200 ID names.
Save and close Excel.

Here is a mockup image of the end result when blocks with IDs “A002”, “A006”, “A009”, “A012” are found in the attached drawing. (whoops. Cant figure out how to attach a CAD file or excel file here. Sorry. Anyone know?)

What I know:

a) I have a set number of attribute ID names (1 through 200) that I will ever encounter so I know all the possible names and the rows it need to go to. (Ex: ID “A006” goes in row 10).

b) I can make a Putcell command ready for each of those ID names to cover all (1 through 200).

What I don’t know:

c) How to associate block ID name with a row.

d) How to find duplicate block count and associate it with block ID name/row.

e) How to add ID name and count to a putcell command associated by row like this: (PutCell "B10" '("A006" 3)). "A006" will always go with row 10. What Attribute ID names found and the count is the variable that changes for each drawing.

Routine from Getexcel.lsp: Credit Terry Miller

(defun c:MyPricing ()

(defun GetExcel (ExcelFile$ SheetName$ MaxRange$ / Cnt# Column# ColumnRow@ CreateLists:
  CurRegion Data@ ExcelRange^ ExcelValue ExcelVariant^ Max_Range$ MaxColumn# MaxRow#
  Range$ Row# Sheet_Name$ Worksheet)
  ;-----------------------------------------------------------------------------
  ; CreateLists: - Creates Lists of SheetName$ up to MaxRange$ of Excel data
  ;-----------------------------------------------------------------------------
  (defun CreateLists: (Sheet_Name$ Max_Range$ / ReturnList@)
    (if Sheet_Name$
      (vlax-for Worksheet (vlax-get-property *ExcelApp% "Sheets")
        (if (= (vlax-get-property Worksheet "Name") Sheet_Name$)
          (vlax-invoke-method Worksheet "Activate")
        );if
      );vlax-for
    );if
    (if Max_Range$
      (progn
        (setq ColumnRow@ (ColumnRow Max_Range$))
        (setq MaxColumn# (nth 0 ColumnRow@))
        (setq MaxRow# (nth 1 ColumnRow@))
      );progn
      (progn
        (setq CurRegion (vlax-get-property (vlax-get-property
          (vlax-get-property *ExcelApp% "ActiveSheet") "Range" "A1") "CurrentRegion")
        );setq
        (setq MaxRow# (vlax-get-property (vlax-get-property CurRegion "Rows") "Count"))
        (setq MaxColumn# (vlax-get-property (vlax-get-property CurRegion "Columns") "Count"))
      );progn
    );if
    (setq Row# 1)
    (repeat MaxRow#
      (setq Data@ nil)
      (setq Column# 1)
      (repeat MaxColumn#
        (setq Range$ (strcat (Number2Alpha Column#)(itoa Row#)))
        (setq ExcelRange^ (vlax-get-property *ExcelApp% "Range" Range$))
        (setq ExcelVariant^ (vlax-get-property ExcelRange^ 'Value))
        (setq ExcelValue (vlax-variant-value ExcelVariant^))
        (setq ExcelValue
          (cond
            ((= (type ExcelValue) 'INT) (itoa ExcelValue))
            ((= (type ExcelValue) 'REAL) (rtosr ExcelValue))
            ((= (type ExcelValue) 'STR) (vl-string-trim " " ExcelValue))
            ((/= (type ExcelValue) 'STR) "")
          );cond
        );setq
        (setq Data@ (append Data@ (list ExcelValue)))
        (setq Column# (1+ Column#))
      );repeat
      (setq ReturnList@ (append ReturnList@ (list Data@)))
      (setq Row# (1+ Row#))
    );repeat
    ReturnList@
  );defun CreateLists:
  ;-----------------------------------------------------------------------------
  (if (= (type ExcelFile$) 'STR)
    (if (not (findfile ExcelFile$))
      (progn
        (alert (strcat "Excel file " ExcelFile$ " not found."))
        (exit)
      );progn
    );if
    (progn
      (alert "Excel file not specified.")
      (exit)
    );progn
  );if
  (gc)
  (if (setq *ExcelApp% (vlax-get-object "Excel.Application"))
    (progn
      (vlax-release-object *ExcelApp%)(gc)
    );progn
  );if
  (setq ExcelFile$ (findfile ExcelFile$))
  (setq *ExcelApp% (vlax-get-or-create-object "Excel.Application"))
  (vlax-invoke-method (vlax-get-property *ExcelApp% 'WorkBooks) 'Open ExcelFile$)
  (setq *ExcelData@ nil)
  (if (= (type SheetName$) 'LIST)
    (progn
      (if (/= (type MaxRange$) 'LIST)
        (setq MaxRange$ (list MaxRange$))
      );if
      (setq Cnt# 0)
      (repeat (length SheetName$)
        (setq Sheet_Name$ (nth Cnt# SheetName$))
        (setq Max_Range$ (nth Cnt# MaxRange$))
        (princ (strcat "\nImporting " (vl-filename-base ExcelFile$) " - " Sheet_Name$ " data..."))(princ)
        (setq ReturnList@ (CreateLists: Sheet_Name$ Max_Range$))
        (setq *ExcelData@ (append *ExcelData@ (list ReturnList@)))
        (setq Cnt# (1+ Cnt#))
      );repeat
    );progn
    (progn
      (if SheetName$
        (progn (princ (strcat "\nImporting " (vl-filename-base ExcelFile$) " - " SheetName$ " data..."))(princ))
      );if
      (setq *ExcelData@ (CreateLists: SheetName$ MaxRange$))
    );progn
  );if
  (vlax-invoke-method (vlax-get-property *ExcelApp% "ActiveWorkbook") 'Close :vlax-False)
  (vlax-invoke-method *ExcelApp% 'Quit)
  (vlax-release-object *ExcelApp%)(gc)
  (setq *ExcelApp% nil)
  *ExcelData@
);defun GetExcel
;-------------------------------------------------------------------------------
; GetCell - Returns the cell value from the *ExcelData@ list
; Arguments: 1
;   Cell$ = Cell ID
; Syntax example: (GetCell "E19") = value of cell E19
;-------------------------------------------------------------------------------
(defun GetCell (Cell$ / Column# ColumnRow@ Return Row#)
  (setq ColumnRow@ (ColumnRow Cell$))
  (setq Column# (1- (nth 0 ColumnRow@)))
  (setq Row# (1- (nth 1 ColumnRow@)))
  (setq Return "")
  (if *ExcelData@
    (if (and (>= (length *ExcelData@) Row#)(>= (length (nth 0 *ExcelData@)) Column#))
      (setq Return (nth Column# (nth Row# *ExcelData@)))
    );if
  );if
  Return
);defun GetCell
;-------------------------------------------------------------------------------
; OpenExcel - Opens an Excel spreadsheet
; Arguments: 3
;   ExcelFile$ = Excel filename or nil for new spreadsheet
;   SheetName$ = Sheet name or nil for not specified
;   Visible = t for visible or nil for hidden
; Syntax examples:
; (OpenExcel "C:\\Temp\\Temp.xlsx" "Sheet2" t) = Opens C:\Temp\Temp.xlsx on Sheet2 as visible session
; (OpenExcel "C:\\Temp\\Temp.xlsx" nil nil) = Opens C:\Temp\Temp.xlsx on current sheet as hidden session
; (OpenExcel nil "Parts List" nil) =  Opens a new spreadsheet and creates a Part List sheet as hidden session
;-------------------------------------------------------------------------------
(defun OpenExcel (ExcelFile$ SheetName$ Visible / Sheet$ Sheets@ Worksheet)
  (if (= (type ExcelFile$) 'STR)
    (if (findfile ExcelFile$)
      (setq *ExcelFile$ ExcelFile$)
      (progn
        (alert (strcat "Excel file " ExcelFile$ " not found."))
        (exit)
      );progn
    );if
    (setq *ExcelFile$ "")
  );if
  (gc)
  (if (setq *ExcelApp% (vlax-get-object "Excel.Application"))
    (progn
      (vlax-release-object *ExcelApp%)(gc)
    );progn
  );if
  (setq *ExcelApp% (vlax-get-or-create-object "Excel.Application"))
  (if ExcelFile$
    (if (findfile ExcelFile$)
      (vlax-invoke-method (vlax-get-property *ExcelApp% 'WorkBooks) 'Open ExcelFile$)
      (vlax-invoke-method (vlax-get-property *ExcelApp% 'WorkBooks) 'Add)
    );if
    (vlax-invoke-method (vlax-get-property *ExcelApp% 'WorkBooks) 'Add)
  );if
  (if Visible
    (vla-put-visible *ExcelApp% :vlax-true)
  );if
  (if (= (type SheetName$) 'STR)
    (progn
      (vlax-for Sheet$ (vlax-get-property *ExcelApp% "Sheets")
        (setq Sheets@ (append Sheets@ (list (vlax-get-property Sheet$ "Name"))))
      );vlax-for
      (if (member SheetName$ Sheets@)
        (vlax-for Worksheet (vlax-get-property *ExcelApp% "Sheets")
          (if (= (vlax-get-property Worksheet "Name") SheetName$)
            (vlax-invoke-method Worksheet "Activate")
          );if
        );vlax-for
        (vlax-put-property (vlax-invoke-method (vlax-get-property *ExcelApp% "Sheets") "Add") "Name" SheetName$)
      );if
    );progn
  );if
  (princ)
);defun OpenExcel
;-------------------------------------------------------------------------------
; PutCell - Put values into Excel cells
; Arguments: 2
;   StartCell$ = Starting Cell ID
;   Data@ = Value or list of values
; Syntax examples:
; (PutCell "A1" "PART NUMBER") = Puts PART NUMBER in cell A1
; (PutCell "B3" '("Dim" 7.5 "9.75")) = Starting with cell B3 put Dim, 7.5, and 9.75 across
;-------------------------------------------------------------------------------
(defun PutCell (StartCell$ Data@ / Cell$ Column# ExcelRange Row#)
  (if (= (type Data@) 'STR)
    (setq Data@ (list Data@))
  )
  (setq ExcelRange (vlax-get-property *ExcelApp% "Cells"))
  (if (Cell-p StartCell$)
    (setq Column# (car (ColumnRow StartCell$))
          Row# (cadr (ColumnRow StartCell$))
    );setq
    (if (vl-catch-all-error-p
          (setq Cell$ (vl-catch-all-apply 'vlax-get-property
            (list (vlax-get-property *ExcelApp% "ActiveSheet") "Range" StartCell$))
          );setq
        );vl-catch-all-error-p
        (alert (strcat "The cell ID \"" StartCell$ "\" is invalid."))
        (setq Column# (vlax-get-property Cell$ "Column")
              Row# (vlax-get-property Cell$ "Row")
        );setq
    );if
  );if
  (if (and Column# Row#)
    (foreach Item Data@
      (vlax-put-property ExcelRange "Item" Row# Column# (vl-princ-to-string Item))
      (setq Column# (1+ Column#))
    );foreach
  );if
  (princ)
);defun PutCell
;-------------------------------------------------------------------------------
; CloseExcel - Closes Excel spreadsheet
; Arguments: 1
;   ExcelFile$ = Excel saveas filename or nil to close without saving
; Syntax examples:
; (CloseExcel "C:\\Temp\\Temp.xlsx") = Saveas C:\Temp\Temp.xlsx and close
; (CloseExcel nil) = Close without saving
;-------------------------------------------------------------------------------
(defun CloseExcel (ExcelFile$ / Saveas)
  (if ExcelFile$
    (if (= (strcase ExcelFile$) (strcase *ExcelFile$))
      (if (findfile ExcelFile$)
        (vlax-invoke-method (vlax-get-property *ExcelApp% "ActiveWorkbook") "Save")
        (setq Saveas t)
      );if
      (if (findfile ExcelFile$)
        (progn
          (vl-file-delete (findfile ExcelFile$))
          (setq Saveas t)
        );progn
        (setq Saveas t)
      );if
    );if
  );if
  (if Saveas
    (vlax-invoke-method (vlax-get-property *ExcelApp% "ActiveWorkbook")
      "SaveAs" ExcelFile$ -4143 "" "" :vlax-false :vlax-false nil
    );vlax-invoke-method
  );if
  (vlax-invoke-method (vlax-get-property *ExcelApp% "ActiveWorkbook") 'Close :vlax-False)
  (vlax-invoke-method *ExcelApp% 'Quit)
  (vlax-release-object *ExcelApp%)(gc)
  (setq *ExcelApp% nil *ExcelFile$ nil)
  (princ)
);defun CloseExcel
;-------------------------------------------------------------------------------
; ColumnRow - Returns a list of the Column and Row number
; Function By: Gilles Chanteau from Marseille, France
; Arguments: 1
;   Cell$ = Cell ID
; Syntax example: (ColumnRow "ABC987") = '(731 987)
;-------------------------------------------------------------------------------
(defun ColumnRow (Cell$ / Column$ Char$ Row#)
  (setq Column$ "")
  (while (< 64 (ascii (setq Char$ (strcase (substr Cell$ 1 1)))) 91)
    (setq Column$ (strcat Column$ Char$)
          Cell$ (substr Cell$ 2)
    );setq
  );while
  (if (and (/= Column$ "") (numberp (setq Row# (read Cell$))))
    (list (Alpha2Number Column$) Row#)
    '(1 1);default to "A1" if there's a problem
  );if
);defun ColumnRow
;-------------------------------------------------------------------------------
; Alpha2Number - Converts Alpha string into Number
; Function By: Gilles Chanteau from Marseille, France
; Arguments: 1
;   Str$ = String to convert
; Syntax example: (Alpha2Number "ABC") = 731
;-------------------------------------------------------------------------------
(defun Alpha2Number (Str$ / Num#)
  (if (= 0 (setq Num# (strlen Str$)))
    0
    (+ (* (- (ascii (strcase (substr Str$ 1 1))) 64) (expt 26 (1- Num#)))
       (Alpha2Number (substr Str$ 2))
    );+
  );if
);defun Alpha2Number
;-------------------------------------------------------------------------------
; Number2Alpha - Converts Number into Alpha string
; Function By: Gilles Chanteau from Marseille, France
; Arguments: 1
;   Num# = Number to convert
; Syntax example: (Number2Alpha 731) = "ABC"
;-------------------------------------------------------------------------------
(defun Number2Alpha (Num# / Val#)
  (if (< Num# 27)
    (chr (+ 64 Num#))
    (if (= 0 (setq Val# (rem Num# 26)))
      (strcat (Number2Alpha (1- (/ Num# 26))) "Z")
      (strcat (Number2Alpha (/ Num# 26)) (chr (+ 64 Val#)))
    );if
  );if
);defun Number2Alpha
;-------------------------------------------------------------------------------
; Cell-p - Evaluates if the argument Cell$ is a valid cell ID
; Function By: Gilles Chanteau from Marseille, France
; Arguments: 1
;   Cell$ = String of the cell ID to evaluate
; Syntax examples: (Cell-p "B12") = t, (Cell-p "BT") = nil
;-------------------------------------------------------------------------------
(defun Cell-p (Cell$)
  (and (= (type Cell$) 'STR)
    (or (= (strcase Cell$) "A1")
      (not (equal (ColumnRow Cell$) '(1 1)))
    );or
  );and
);defun Cell-p
;-------------------------------------------------------------------------------
; Row+n - Returns the cell ID located a number of rows from cell
; Function By: Gilles Chanteau from Marseille, France
; Arguments: 2
;   Cell$ = Starting cell ID
;   Num# = Number of rows from cell
; Syntax examples: (Row+n "B12" 3) = "B15", (Row+n "B12" -3) = "B9"
;-------------------------------------------------------------------------------
(defun Row+n (Cell$ Num#)
  (setq Cell$ (ColumnRow Cell$))
  (strcat (Number2Alpha (car Cell$)) (itoa (max 1 (+ (cadr Cell$) Num#))))
);defun Row+n
;-------------------------------------------------------------------------------
; Column+n - Returns the cell ID located a number of columns from cell
; Function By: Gilles Chanteau from Marseille, France
; Arguments: 2
;   Cell$ = Starting cell ID
;   Num# = Number of columns from cell
; Syntax examples: (Column+n "B12" 3) = "E12", (Column+n "B12" -1) = "A12"
;-------------------------------------------------------------------------------
(defun Column+n (Cell$ Num#)
  (setq Cell$ (ColumnRow Cell$))
  (strcat (Number2Alpha (max 1 (+ (car Cell$) Num#))) (itoa (cadr Cell$)))
);defun Column+n
;-------------------------------------------------------------------------------
; rtosr - Used to change a real number into a short real number string
; stripping off all trailing 0's.
; Arguments: 1
;   RealNum~ = Real number to convert to a short string real number
; Returns: ShortReal$ the short string real number value of the real number.
;-------------------------------------------------------------------------------
(defun rtosr (RealNum~ / DimZin# ShortReal$)
  (setq DimZin# (getvar "DIMZIN"))
  (setvar "DIMZIN" 8)
  (setq ShortReal$ (rtos RealNum~ 2 8))
  (setvar "DIMZIN" DimZin#)
  ShortReal$
);defun rtosr
;-------------------------------------------------------------------------------
(princ);End of GetExcel.lsp


(OpenExcel "C:\\TEMP\\MyBlock_Att_Test.xlsx" "MainSheet" nil);Open Excel file

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
; This is where I need help finding block info and adding to Putcell command
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;;;Example of putting in info from CAD to excel;;;;;
(PutCell "B10" '("A006" 3))
;;;;Example;;;;;


(CloseExcel "C:\\TEMP\\MyBlock_Att_Test.xlsx");Close Excel
);End MyPricing

Here are some routines I was looking at in order to grab the block info needed.

Block attribute ID name:

Here is one from Lee Mac that finds Attribute IDs and saves to variable “data”. I took out the excel export part since that can be handled by Getexcel.lsp routine.

;; Text 2 CSV  -  Lee Mac
;; Writes all Text, MText & Attribute content from all layouts and within
;; all blocks and nested blocks to a selected CSV file.

(defun c:txt2csv ( / data file )
    (cond
        (   (not
                (progn
                    (vlax-for block (vla-get-blocks (vla-get-activedocument (vlax-get-acad-object)))
                        (if (eq :vlax-false (vla-get-isxref block))
                            (vlax-for obj block
                                (cond
                                    (   (wcmatch (vla-get-objectname obj) "AcDb*Text")
                                        (setq data (cons (vla-get-textstring obj) data))
                                    )
                                    (   (and
                                            (eq "AcDbBlockReference" (vla-get-objectname obj))
                                            (eq :vlax-true (vla-get-hasattributes obj))
                                        )
                                        (foreach att (vlax-invoke obj 'getattributes)
                                            (setq data (cons (vla-get-textstring att) data))
                                        )
                                    )
                                )
                            )
                        )
                    )
                    data
                )
            )
            (princ "\nNo Text, MText or Attributes found.")
        )
        (   (not (setq file (getfiled "Create CSV file" "" "csv" 1)))
            (princ "\n*Cancel*")
        )
        (   (setq file (open file "w"))
            (foreach x data (write-line x file))
            (setq file (close file))
            (princ (strcat "\n" (itoa (length data)) " strings written to file."))
        )
        (   (princ "\nUnable to open CSV file for writing."))
    )
    (princ)
)
(vl-load-com) (princ)

Block count.

Here is another one from Lee Mac that list the count by its block name (but not attribute ID). Block Name will already be listed in correct row under column A in spreadsheet. Just need to add corresponding ID name ex: “A006” in Column B/row 10 and count “3” in Column C/row 10 through the Putcell command. EX: (PutCell "B10" '("A006" 3)). I guess I could associate Block name to Block Attribute ID and also to row. The count would be the only variable really thats unknown for each.

(defun c:myblockcounter ( / blk idx itm lst sel )
    (if (setq sel (ssget '((0 . "INSERT"))))
        (repeat (setq idx (sslength sel))
            (setq blk (cdr (assoc 2 (entget (ssname sel (setq idx (1- idx)))))))
            (if (setq itm (assoc blk lst))
                (setq lst (subst (cons blk (1+ (cdr itm))) itm lst))
                (setq lst (cons  (cons blk 1) lst))
            )
        )
    )
    (foreach itm lst (princ (strcat "\n" (car itm) ": " (itoa (cdr itm)))))
    (princ)
)

Any help is greatly appreciate!

Re: AutoLisp to pass block attribute ID name and block count of a specific row to “Putcell” command for the GetExcel routine

Sea-Haven — Sun, 15 Aug 2021 03:36:49 GMT

This is not to excel but to a table similar function look at variable lst3 its holds a list of name & qty. Some table values are set to end user sizes. It uses a simple just read say a block attribute with a dummy counter (attname1 1)(attname2 1)....... end up with (attname1 6)(attname2 3)

; modified aug 2021 By AlanH ; example of creating a table using passed variables ; By Alan H July 2017 (defun AH:table_make (numcolumns numrows txtsz colwidth / numrows curspc colwidth numcolumns numrows objtable rowheight sp doc) (vl-load-com) (setq sp (vlax-3d-point (getpoint "Pick top left"))); or use getpoint (setq doc (vla-get-activedocument (vlax-get-acad-object) )) (if (= (vla-get-activespace doc) 0) (setq curspc (vla-get-paperspace doc)) (setq curspc (vla-get-modelspace doc)) ) (setq rowheight (* 2.0 txtsz)) (setq objtable (vla-addtable curspc sp numrows numcolumns rowheight colwidth)) (vla-settext objtable 0 0 "TABLE title") (vla-SetTextHeight Objtable (+ acDataRow acTitleRow acHeaderRow) txtsz) (setq i -1) (setq a 64) (repeat numcolumns (vla-setcolumnwidth objtable (setq i (+ i 1)) colwidth) (vla-SetText Objtable 1 i (chr (setq a (+ a 1)))) ) (vla-SetText Objtable 1 i "Count") ; (command "_zoom" "e") (princ) ) ; Make a count of common items ; By AlanH Aug 2021 (defun my-count (a L) (cond ((null L) 0) ((equal a (car L)) (+ 1 (my-count a (cdr L)))) (t (my-count a (cdr L)))) ) ; By Gile (defun remove_doubles (lst) (if lst (cons (car lst) (remove_doubles (vl-remove (car lst) lst))) ) ) (defun c:test ( / ss lst lt2 lst3 txt ) (setq ss (ssget (list (cons 0 "TEXT")))) (if (= ss nil) (alert "no text picked") (progn (setq lst '() lst3 '()) (repeat (setq x (sslength ss)) (setq txt (cdr (assoc 1 (entget (ssname ss (setq x (1- x))))))) (setq lst (cons txt lst)) ) (setq lst2 (remove_doubles lst)) (foreach val lst2 (setq cnt (my-count val lst)) (setq lst3 (cons (list val cnt) lst3)) ) ) ) (princ "\n") (setq n 2 txtht 45) ( AH:table_make 2 n txtht 400) (setq objtable (vlax-ename->vla-object (entlast))) (foreach val lst3 (vla-insertrows objtable (setq n (1+ n)) (* txtht 2.0) 1) (vla-settext objtable (1+ n) 0 (car val)) (vla-settext objtable (1+ n) 1 (cadr (rtos val 2 0))) (vla-setrowheight objtable (1+n) (* txtht 2.0)) ) (princ) ) (c:test)

It has a couple of nice functions similar to Lee's block count.

Re: AutoLisp to pass block attribute ID name and block count of a specific row to “Putcell” command for the GetExcel routine

rebPTFNF — Sun, 15 Aug 2021 16:08:27 GMT

I appreciate the reply and thank you for the code. Unfortunately, I can’t seem to get the count to work in my drawing. Perhaps I’m not doing something right.

It doesn’t recognize my blocks when I select them. I get the alert "no text picked". Is it’s looking for text or blocks? The table appears but is empty. I will keep trying…

Do you know if there is a way to upload files to a post? It may be helpful to upload my simple DWG and Excel test files for others to try. Thanks again for your help. Any information is appreciated!

Re: AutoLisp to pass block attribute ID name and block count of a specific row to “Putcell” command for the GetExcel routine

Sea-Haven — Mon, 16 Aug 2021 00:09:48 GMT

There is a problem with the forum, you should see a drag to here files a bit further down the screen. Drag your dwg and excel to it use wblock 1st for the dwg as it will cut out a small bit for you do not need total dwg.

I can not see it at moment. Hopefully Autodesk will fix.

I posted then hit edit so got the drag to

Re: AutoLisp to pass block attribute ID name and block count of a specific row to “Putcell” command for the GetExcel routine

rebPTFNF — Thu, 19 Aug 2021 18:35:56 GMT

I think the attachments are working now. Let me know if you don't see the Excel and DWG example files. Thanks!

Re: AutoLisp to pass block attribute ID name and block count of a specific row to “Putcell” command for the GetExcel routine

Sea-Haven — Sun, 22 Aug 2021 06:01:50 GMT

This is just do the count part, if want the ID attribute will need to add.

(defun my-count (a L) (cond ((null L) 0) ((equal a (car L)) (+ 1 (my-count a (cdr L)))) (t (my-count a (cdr L)))) ) ; By Gile (defun remove_doubles (lst) (if lst (cons (car lst) (remove_doubles (vl-remove (car lst) lst))) ) ) (defun c:test ( / ss lst lt2 lst3 txt ) (setq ss (ssget (list (cons 0 "INSERT")))) (if (= ss nil) (alert "no text picked") (progn (setq lst '() lst3 '()) (repeat (setq x (sslength ss)) (setq txt (cdr (assoc 2 (entget (ssname ss (setq x (1- x))))))) (setq lst (cons txt lst)) ) (setq lst2 (remove_doubles lst)) (foreach val lst2 (setq cnt (my-count val lst)) (setq lst3 (cons (list val cnt) lst3)) ) ) ) (princ) )

Look at lst3. Command: !lst3
(("MyBlockA012" 1) ("MyBlockA009" 1) ("MyBlockA002" 2) ("MyblockA006" 3))

Just a suggestion getexcel.lsp does not need to live in your code you just need 1 line of code.

(if (not openexcel)(load "getexcel.lsp"))

Re: AutoLisp to pass block attribute ID name and block count of a specific row to “Putcell” command for the GetExcel routine

rebPTFNF — Sun, 22 Aug 2021 18:55:41 GMT

Thank you so much for your response.

I believe I am doing something wrong or missing a step. I ran your code on my CAD file and selected the blocks. I then got the value of 1st3 (!1st3) at prompt. It comes back as Nil.

I then ran the full GetExcel.lsp and then your code, just incase I missed something, but got the same result “nil”.

I can’t seem to get the listing at the command prompt of block counts (Ex: MyblockA012” 1…etc).

Im most likely misunderstanding something or misunderstood your directions.

Just to clarify:

When you run your code in the CAD with the blocks and then you get the value of (!1st3) at the command prompt, does the prompt show the following?

((“myblockA012” 1)(“MyBlockA009” 1)(“MyBlockA002” 2)(“MyBlockA006” 3)).

I'm only getting “nil”. I tried inserting the blocks into a new drawing and running code again but I still got nil for value “1st3”. Ugh. Probably something wrong on my end...

Last, any idea once you get the block counts or ID tags values, on how to run it through the Putcell function of the Getexcel.lsp routine? That part really has me stumped.

The manual code to put in values for example is: (putcell “B10” ‘(“A006” 3))

If I have the variable “1st3” for the block count values I would think I could do something like this: (putcell “B10” ‘(“A006” “1st3”)) at the end of the routine. The Excel file seems to only put the text “1st3” in the cell instead of showing the value of “1st3”. I've been looking all over but cant find an example of implementing the putcell function using variables.

Your assistance has been greatly appreciated…!

Re: AutoLisp to pass block attribute ID name and block count of a specific row to “Putcell” command for the GetExcel routine

Sea-Haven — Mon, 23 Aug 2021 02:35:01 GMT

To peak at a lisp variable just type this only !lst3 you can then use a foreach and a car and cadr to get block name and count.

(foreach val lst3 (alert (strcat "Block name "(car val)"\n\n Count " (rtos (cadr val) 2 0))) )

Re: AutoLisp to pass block attribute ID name and block count of a specific row to “Putcell” command for the GetExcel routine

rebPTFNF — Tue, 24 Aug 2021 03:31:03 GMT

Thanks for the reply. Unfortunately, I still keep getting nil when I try to get the value of the variable lst3. Please see attached image at my command prompt when I run the code on my DWG file. Can you by chance show the result of what is shown at your command prompt when you get the value of lst3? ... Just so I know what I should be seeing when it works.

Thank you for your patients helping me get this part working so I can move forward with the rest...

🙂

Re: AutoLisp to pass block attribute ID name and block count of a specific row to “Putcell” command for the GetExcel routine

Sea-Haven — Tue, 24 Aug 2021 04:34:58 GMT

I resaved the lisp I use notepad++ and sometimes there can be a issue between saving as plain ansi code and utf-8 try this. Worked on your sample dwg added and erased blocks when testing.

Re: AutoLisp to pass block attribute ID name and block count of a specific row to “Putcell” command for the GetExcel routine

pbejse — Tue, 24 Aug 2021 06:30:14 GMT

A couple of questions for you:

Is there one xlsx file per drawing? or is there one template and then save a copy per drawing?
If there is a template, the blockname should match the one on the drawing, unlike on your example, the name of the block is "Myblock001" etc.. and on the drawing its "MyblockA001".
If there are no match found comapring with with the xlsx file but there are valid blocks found on the drawing should that be added? issue here is where would be the data be coming from like for "Cost per"?

Re: AutoLisp to pass block attribute ID name and block count of a specific row to “Putcell” command for the GetExcel routine

Sea-Haven — Tue, 24 Aug 2021 07:27:16 GMT

The task is getting complicated, as Pbe has mentioned how do you propose that you know block006 goes to the 10th row ?

It would need some form of look up list (("block001" 4)("block002" 5).... or a text file would be better as you hint at 200 items.

Its starting to look more and more like a please donate task.

Anyway step 1.

(setq blst (list '("BlockA001" 4)'("BlockA002" 5)'("BlockA003" 6)'("BlockA004" 7)'("BlockA005" 9) '("BlockA006" 10)'("BlockA007" 11)'("BlockA008" 12)'("BlockA009" 13)'("BlockA0010" 14) '("BlockA0011" 16)'("BlockA0012" 17)'("BlockA0013" 18) )) (defun my-count (a L) (cond ((null L) 0) ((equal a (car L)) (+ 1 (my-count a (cdr L)))) (t (my-count a (cdr L)))) ) ; By Gile (defun remove_doubles (lst) (if lst (cons (car lst) (remove_doubles (vl-remove (car lst) lst))) ) ) (defun c:test ( / ss lst lt2 lst3 txt ) (setq ss (ssget (list (cons 0 "INSERT")))) (if (= ss nil) (alert "no text picked") (progn (setq lst '() lst3 '()) (repeat (setq x (sslength ss)) (setq blk (vlax-ename->vla-object (ssname ss (setq x (1- x))))) (setq bname (vla-get-name blk)) (foreach att (vlax-invoke blk 'getattributes) (if (= (vla-get-tagstring att) "ID") (setq ID (vla-get-textstring att)) ) ) (setq lst (cons (list bname id) lst)) ) (setq lst2 (remove_doubles lst)) (foreach val lst2 (setq cnt (my-count val lst)) (setq lst3 (cons (list val cnt) lst3)) ) ) ) (princ lst3) (princ) )

For Pbe dont worry about cost. But lots of other questions. I can just see now do this.

Re: AutoLisp to pass block attribute ID name and block count of a specific row to “Putcell” command for the GetExcel routine

pbejse — Tue, 24 Aug 2021 08:00:53 GMT

@Sea-Haven wrote:

The task is getting complicated, as Pbe has mentioned how do you propose that you know block006 goes to the 10th row ?

That is actually the easy part 🙂, as long as the OP is using an xlsx template and the Block names are found

(defun c:MBWAT ( / dataTarget dataSource blst data i ev atv f fd inc); My_Blocks_With_Att_Tags ;<-- make sure these files location is included on SFP otherwise hard code the path (or (not GetExcel)(load "getexcel.lsp")) (or XlsTemp (setq XlsTemp (findfile "MyBlock_Template.xlsx")));< one with blank "B" column and 0 values at "C" (if (and GetExcel XlsTemp (setq dataTarget (Getfiled "Select Data source" (strcat (getvar 'dwgprefix)(vl-filename-base (getvar 'dwgname))) "xlsx" 1)) ;; the name could be something else, need to know the correct filer ;; (setq dataSource (ssget "_X" '((0 . "INSERT")(66 . 1)(2 . "Myblock###")))) ) (progn (if (findfile dataTarget)(vl-file-delete dataTarget)) (vl-file-copy XlsTemp dataTarget) (setq blst nil data (GetExcel dataTarget "MainSheet" nil)) (repeat (setq i (sslength dataSource)) (setq ev (vlax-ename->vla-object (ssname dataSource (setq i (1- i))))) (setq bname (strcase (vla-get-effectivename ev)) Attv (mapcar '(lambda (at) (list (Vla-get-tagstring at)(Vla-get-textstring at))) (Vlax-invoke ev 'GetAttributes))) (if (null (member bname blst)) (setq blst (Cons bname blst))) (if (and (setq f (assoc "ID" attv)) (setq fd (vl-some '(lambda (dv) (if (eq bname (strcase (Car dv))) dv)) data)) ) (setq data (subst (append (list (Car fd) (cadr f) (itoa (1+ (atoi (caddr fd))))) (cdddr fd)) fd data))) ) (setq inc 3) (OpenExcel dataTarget "MainSheet" nil) (foreach itm (mapcar '(lambda (n) (cons (itoa (Setq inc (1+ inc))) n)) (cdddr data)) (if (member (strcase (Cadr itm)) blst) (progn (PutCell (strcat "B" (car itm)) (Caddr itm)) (PutCell (strcat "C" (car itm)) (Cadddr itm)) ) ) ) (CloseExcel dataTarget) (startapp "explorer" dataTarget) ) ) (princ) )

HTH

Re: AutoLisp to pass block attribute ID name and block count of a specific row to “Putcell” command for the GetExcel routine

rebPTFNF — Tue, 24 Aug 2021 18:37:00 GMT

Printing the command prompt was a great addition. It shows the results for me now.. Here is my screen cap. Thank you!

Re: AutoLisp to pass block attribute ID name and block count of a specific row to “Putcell” command for the GetExcel routine

rebPTFNF — Tue, 24 Aug 2021 20:27:18 GMT

Thank you for the questions pbe.

1. There is one template file and hope to save a copy per drawing. My goal is to fill in Column C (Qty.) with the number of identical block counts (if any are found..). The spreadsheet use a formula to multiply "Count x Price" to update pricing and saves the updated copy to the folder where the drawing lives. (at least that's the goal)...

2. You are 100% correct and that was a mistake on my part. Great catch. I may have been typing too fast and missed it. I made an example file that represents my real excel pricing sheet. I have fixed the issue and will replace the file and image.

3. The Excel template should contain all information that could be found in a drawing that's relevant for pricing so nothing unexpected should affect it. All formulas are built in, as well as the pricing.
With the block name, I can get the row that the count goes on. Then the count updates the pricing. if nothing is found that's relevant, the pricing stays at 0.

I hope those clarifications are what you were asking about. Please let me know otherwise.. Thanks!

Re: AutoLisp to pass block attribute ID name and block count of a specific row to “Putcell” command for the GetExcel routine

Sea-Haven — Wed, 25 Aug 2021 04:45:22 GMT

Pbe your right just read column "A" and get block names very doubtful a block name is called "Pricing" or "Block 1 - 5"

A true xls would be good

Re: AutoLisp to pass block attribute ID name and block count of a specific row to “Putcell” command for the GetExcel routine

pbejse — Wed, 25 Aug 2021 17:34:23 GMT

@rebPTFNF wrote:

1. There is one template file and hope to save a copy per drawing. ..
2. You are 100% correct and that was a mistake on my part. Great catch...
3. The Excel template should contain all information that could be found in a drawing that's relevan...

I hope those clarifications are what you were asking about. Please let me know otherwise.. Thanks!

Thank you for the clarification. Did you get to try the code as post # 13 ?

The program only modifies column "B" & "C". As far as I can tell it meets all the requirements as long as following conditions is adhere to.

The xlsx template is found

(or (not GetExcel)(load "getexcel.lsp"))

The getcell and its support programs are loded

(or XlsTemp (setq XlsTemp (findfile "MyBlock_Template.xlsx")))

Otherwise include the path of both files on the code.

(or (not GetExcel)(load "F:\\Lisp\\getexcel.lsp"))
(or XlsTemp (setq  XlsTemp (findfile  "F:\\Lisp\\SupportFiles\\Templates\\MyBlock_Template.xlsx")))

The number of spokes on your bicycle wheel is an even number.

24, 28, 32, 36, 40 or 48

The hotdog vendor at the corner of 3rd and 8th street solds his 100th hotdog sandwich to a Australian guy who is wearing a shirt with pineapple print and 3 Dollars and 25 cents on his person.

HTH

Re: AutoLisp to pass block attribute ID name and block count of a specific row to “Putcell” command for the GetExcel routine

rebPTFNF — Fri, 27 Aug 2021 03:08:26 GMT

Hey pbejse,

Sorry for the late reply. Thank you for the code! I did try it but not getting it to work at the moment. I didn’t want to give a response until I did, but still not working yet. I need to go back through in detail and make sure I have everything required. I must be missing something. I will work on it and hopefully find the issue. I did get as far as “Select data source” but it doesn’t seem to save the excel file in the folder I choose and cannot find it anywhere on my drive. Not sure what's going on so will start reevaluate all the steps and will make sure I have all files needed and in place… Thank for your patients and your help and will let you know my results as I try again....

Re: AutoLisp to pass block attribute ID name and block count of a specific row to “Putcell” command for the GetExcel routine

pbejse — Fri, 27 Aug 2021 04:49:01 GMT

@rebPTFNF wrote:

Sorry for the late reply. Thank you for the code! I did try it but not getting it to work at the moment. I didn’t want to give a response until I did, but still not working yet...

No need to apologize, Its gratifying to know that you tried to make it work before saying it does not work and thank you for that.

I did get as far as “Select data source” but it doesn’t seem to save the excel file in the folder I choose

Thats a good sign, that means these two variables are found

GetExcel <-- The getexcel program is loaded
XlsTemp <---The Templates is found

That leaves us with this line.

(setq dataSource (ssget "_X" '((0 . "INSERT")(66 . 1)(2 . "Myblock###"))))

Most likey no blocks were found as the ssget function is filtered to look for block names that starts with "Myblock" and with a 3 digit suffix. e.g. "Myblock002". What we can do is remove the filter to select all attribute blocks

(setq dataSource (ssget "_X" '((0 . "INSERT")(66 . 1)))) ; All Blocks without filter

or manually select on screen

(setq dataSource (ssget '((0 . "INSERT")(66 . 1)))) ; Select blocks on screen

or we can even add the corect block names on the filter as shown on the posted code.

Try any of the two options ( All Blocks without filter / Select blocks on screen ) and let me know how it goes.

HTH

Re: AutoLisp to pass block attribute ID name and block count of a specific row to “Putcell” command for the GetExcel routine

rebPTFNF — Sat, 28 Aug 2021 18:29:57 GMT

It works! This is Awesome! Changing the filter did the trick. It finds any matching block from the Excel template, puts in the tag and the count from the drawing. Wow! You are amazing and thank you some much.

I want to thank you and Sea-Haven as well for all your help. I'm going to try a full blown test on the real pricing sheet but I think its going to work.

I have posted this question on many other forums looking for this answer. I would like to post your solution you created for others who might be seeking the same question.... if you don't mind. I wont post it anywhere else if you would rather it just stay here. I can just mark that the question has been answered... But if your ok with it, I can post your solution to the other forums I have sent this question to...

Thanks again!!!