Topic: Inserting Block, but reading from Excel

[debgun]

I need some assistance with generating some lisp code that will insert one block multiple times based on input from excel.

So the excel sheet will look like this (the file format is xlsx):
R1  X coordinate  Y coordinate  Z coordinate  Rotation  X Scale  Y Scale  Z Scale  Layer  Attribute 1 Attribute 2
R2  6                  12               0                 0           1           1          1           2       AB            100
R3  12                 6                0                 90          2           2          2          3        GE            200

I'm trying to use GetExcel.lsp http://web2.airmail.net/terrycad/LISP/GetExcel.lsp
Thanks!

[CHulse]

You might have a look at this:
http://www.theswamp.org/index.php?topic=31317.0

[gile]

Hi,

If you're using Terry's GetExcel, runing:
(GetExcel filename nil nil)
should read the whole used range from A1 cell in the first sheet of the Excel file and return a list:
'(("R1"  "X coordinate" "Y coordinate" "Z coordinate"  "Rotation"  "X Scale"  "Y Scale"  "Z Scale"  "Layer"  "Attribute 1" "Attribute 2")
("R2" "6" "12" "0" "0" "1" "1" "1" "2" "AB" "100")
("R3" "12" "6" "0" "90" "2" "2" "2" "3" "GE" "200"))
This list is stored in a global variable named *ExcelData@ you can iterate through to insert the blocks.

You can also try gc-XlRead...

[debgun]

Thanks for the quick replies. 
Based on what I'm seeing.. The GetExcel is creating a list and storing it in *ExcelData@.  From there I need to use entmake to actual add the blocks.  Please correct me if I'm wrong!

[debgun]

Gile,

I'm having trouble trying to get the GetExcel to work.  I have GetExcel.lsp loaded in drawing in addition to the below.  Unfortunately, I get a error message "error: bad argument type: VLA-OBJECT nil".  I noticed through trace stack that it is getting hung on (vlax-get-property nil "ActiveWorkbook").  Although if I bypass the below code and at commandline (GetExcel filepath nil nil) seems to work perfectly.

Any suggestions?

Code: [Select]

(defun c:blkins (/ item)
  (GetExcel "C:\\Documents and Settings\\dguenthner\\My Documents\\Attribute Info.xlsx" nil nil)
  (setq item (cadr *ExcelData@))
  (print item)
  (entmake (list (cons 0 "Insert")
(cons 100 "AcDbEntity")
(cons 100 "AcDbBlockReference")
(cons 2 car item)
(cons 10 cadr item)
(cons 20 caddr item)
(cons 30 cddddar item)
(cons 41 cdddddar item)
(cons 42 cddddddar item)
(cons 50 cdddddddr item)))
  (CloseExcel nil)
  (princ)
)

[Lee Mac]

I would be more inclined to use "nth" instead of cddddddddddddddddddddddddddddddr ... 

[CAB]

More like this. Untested!!

Code: [Select]

(defun c:blkins (/ AllData data)
  ;|
  '(("R1"  "X coordinate" "Y coordinate" "Z coordinate"  "Rotation"
     "X Scale"  "Y Scale"  "Z Scale"  "Layer"  "Attribute 1" "Attribute 2")
    ("R2" "6" "12" "0" "0" "1" "1" "1" "2" "AB" "100")
    ("R3" "12" "6" "0" "90" "2" "2" "2" "3" "GE" "200"))
    |;
  (setq BlkName "MyBlockName")
  (setq Alldata
         (GetExcel "C:\\Documents and Settings\\dguenthner\\My Documents\\Attribute Info.xlsx" nil nil))
  (print data)
  (foreach data AllData
;; *** with attributes ***
(entmake (list (cons 0 "INSERT") ;***
               (cons 8 (nth 8 data)) ; layer
               (cons 66 1) ; ***  with attributes  <---<<  This is required
               (cons 2 BlkName) ; ***
               (cons 10 (list (nth 1 data)(nth 2 data)(nth 3 data))) ; ***
               (cons 41 (nth 5 data))  ; X scale factor (optional; default = 1)
               (cons 42 (nth 6 data))  ; Y scale factor (optional; default = 1)
               (cons 43 (nth 7 data))  ; Z scale factor (optional; default = 1)
               (cons 44 0.0)  ; Column spacing (optional; default = 0)
               (cons 45 0.0)  ; Row spacing (optional; default = 0)
               (cons 50 (nth 4 data))  ; Rotation angle (optional; default = 0)
               (cons 62 256)  ; Color ByLayer
               (cons 70 0)    ; Column count (optional; default = 1)
               (cons 71 0)    ; Row count (optional; default = 1)
               (cons 210 (list 0 0 1))
               (cons 6 "BYLAYER")))
                 
    (entmake (list (cons 0 "ATTRIB") ;***
              ;;(cons 100 "AcDbEntity")
              ;;(cons 100 "AcDbText")
              ;;(cons 100 "AcDbAttribute")
              (cons 8 (nth 8 data))
               (cons 10 (list 0 0 0)) ;*** Insert Pt
               (cons 40 1) ;***  Height
               (cons 1 (nth 9 data)) ;*** Str
               (cons 2 "TAGNAME") ;*** Tag  <----<<<  ??
               (cons 70 0)
               (cons 73 0)
               (cons 50 0)  ; Rot
               (cons 41 1)
               (cons 51 0)
               (cons 7 "STANDARD") ;*** Text Style
               (cons 71 0)
               (cons 72 0)  ; Just
               (cons 74 0)  ; Just
               (cons 11 (list 0 0 0)) ;***  Align Pt
               (cons 210 (list 0 0 1))
               (cons 62 256)
               (cons 39 0)
               (cons 6 "BYLAYER")))  ; 256
 
    (entmake (list (cons 0 "ATTRIB") ;***
              ;;(cons 100 "AcDbEntity")
              ;;(cons 100 "AcDbText")
              ;;(cons 100 "AcDbAttribute")
              (cons 8 (nth 8 data))
               (cons 10 (list 0 0 0)) ;*** Insert Pt
               (cons 40 1) ;***  Height
               (cons 1 (nth 10 data)) ;*** Str
               (cons 2 "TAGNAME") ;*** Tag    <----<<<  ??
               (cons 70 0)
               (cons 73 0)
               (cons 50 0)  ; Rot
               (cons 41 1)
               (cons 51 0)
               (cons 7 "STANDARD") ;*** Text Style
               (cons 71 0)
               (cons 72 0)  ; Just
               (cons 74 0)  ; Just
               (cons 11 (list 0 0 0)) ;***  Align Pt
               (cons 210 (list 0 0 1))
               (cons 62 256)
               (cons 39 0)
               (cons 6 "BYLAYER")))  ; 256
                 
    (entmake (list (cons 0 "SEQEND") ;***
               (cons 8 "0")))
    )
 
  (CloseExcel nil)
  (princ)
)

[debgun]

Cab,
Thanks for the code, but it is getting stuck on (cons 10 (list (nth 1 data)(nth 2 data)(nth 3 data))).  The error message is "error: too many arguments".  So I tried breaking the coordinates out as shown below.

Code: [Select]

(cons 10 (atof (nth 1 data)))
(cons 20 (atof (nth 2 data)))
(cons 30 (atof (nth 3 data)))
Unfortunately, the error message I get with this is "error: bad DXF group: (10 . 6.0)".  Any suggestions?

Thanks!

[CAB]

Oops, as I said I didn't test it.
Use this for string to real conversion for points.

Code: [Select]

(cons 10 (mapcar 'atof (list (nth 1 data)(nth 2 data)(nth 3 data))))
Use the one you posted for single numbers.

There are additional problems you will encounter.
You need additional attribute information as well.

[debgun]

Cab,

I really appreciate your help.  I've spent several days trying to figure out how to insert a block with attributes.  With no luck, I can't seem to get the block inserted.  You mentioned that I'll need additional information for the attributes.  What should I be looking for?  I've been through the DXF code for Attrib and entered all the codes that are not optional, but no luck.

In addition, I'm having trouble with CloseExcel.  It is getting stuck on

Code: [Select]

(vlax-invoke-method (vlax-get-property *ExcelApp% "ActiveWorkbook") 'Close :vlax-False  The error message is "bad argument type: VLA-OBJECT nil".  I'm calling the command like this "(CloseExcel nil)".

[CAB]

Don't have any time today but I think I would use the Command INSERT to simplify with the attributes .
Just to get the code working!

[Lee Mac]

If you are completely stuck, you could either copy/paste from excel to a txt, or even save the excel as a CSV and use my Point Manager to insert the blocks with attributes (as Cary has posted). 

Just another option 

[debgun]

Thanks, Lee.  I'll give that a try.

[HofCAD]

Dear Readers,

The insertion of blocks is also possible with
the Dynamic Data Exchange (DDE) method with AutoCAD
and Excel.
See http://forums.augi.com/showthread.php?t=112689&page=2
Open drawing HappyTot.dwg with block Happy.
Enable macros in the office document HappyBlocks.xls, and click on the
button 'Macro InsertBlocks' for two inserts of the block 'Happy'
with 2 attributes in the drawing HappyTot.dwg

Regards, HofCAD CSI.

[debgun]

hofCAD,

Thanks for your suggestion.  Unfortunately, I couldn't get the attribute values entered into the block's attribute.  After reviewing AutoCAD's commandline, I noticed the attribute values were being called, but AutoCAD couldn't locate a command.  So after the rotation values was supplied the insert command ended and the attribute value was entered at the command prompt.  Do I have to adjust something in AutoCAD to get the insert command to recognize the following attributes?

BTW I had a difficult time reading the comments.  What language was the vba written in?
Thanks, Debbie