TheSwamp
Code Red => AutoLISP (Vanilla / Visual) => Topic started by: debgun on January 05, 2010, 11:12:07 AM
-
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 (http://web2.airmail.net/terrycad/LISP/GetExcel.lsp)
Thanks!
-
You might have a look at this:
http://www.theswamp.org/index.php?topic=31317.0
-
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 (http://www.theswamp.org/index.php?topic=31441.0)...
-
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!
-
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?
(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)
)
-
I would be more inclined to use "nth" instead of cddddddddddddddddddddddddddddddr ... :wink:
-
More like this. Untested!!
(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)
)
-
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.
(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!
-
Oops, as I said I didn't test it.
Use this for string to real conversion for points.
(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.
-
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 (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)".
-
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! 8-)
-
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). :wink:
Just another option :-)
-
Thanks, Lee. I'll give that a try.
-
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.
-
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
-
You might try this. {Untested}
(defun c:blkins (/ AllData data *error*)
;|
ATTDIA
Controls whether the -INSERT command uses a dialog box for attribute value entry. See "INSERT Command Line."
0 Issues prompts on the command line
1 Uses a dialog box
ATTMODE
Controls display of attributes.
0 Off: Makes all attributes invisible
1 Normal: Retains current visibility of each attribute: visible attributes are
displayed; invisible attributes are not
2 On: Makes all attributes visible
ATTREQ
Determines whether the INSERT command uses default attribute settings during insertion of blocks.
0 Assumes the defaults for the values of all attributes
1 Turns on prompts or dialog box for attribute values, as specified by ATTDIA
TEXTEVAL
Controls the method of evaluation of text strings.
0 All responses to prompts for text strings and attribute values are taken literally
1 Text starting with an opening parenthesis [ ( ] or an exclamation mark (!) is
evaluated as an AutoLISP expression, as for nontextual input
|;
;; error function & Routine Exit
(defun *error* (msg)
(if
(not
(member
msg
'("console break" "Function cancelled" "quit / exit abort" "")
)
)
(princ (strcat "\nError: " msg))
) ; endif
(and sysattdia (setvar "ATTDIA" sysattdia)) ; reset vars
(and sysattreq (setvar "ATTREQ" sysattreq))
(and systxteva (setvar "TEXTEVAL" systxteva))
(setq sysattdia nil)
(setq sysattreq nil)
(setq systxteva nil)
)
;; Start Here -----------------
(setq sysattdia (getvar "ATTDIA"))
(setq sysattreq (getvar "ATTREQ"))
(setq systxteva (getvar "TEXTEVAL"))
(setvar "ATTDIA" 0)
(setvar "ATTREQ" 1)
(setvar "TEXTEVAL" 0)
;|
'(("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
(command "-INSERT" BlkName
"X" (nth 5 data) "Y" (nth 6 data) "Z" (nth 7 data)
"_none" (list (nth 1 data)(nth 2 data)(nth 3 data))
(nth 4 data) ; Rotation angle (optional; default = 0)
(nth 9 data)
(nth 10 data)
)
(command "_.chprop" (entlast) "" "LA" (nth 8 data) "")
)
(CloseExcel nil)
(princ)
)
-
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
Debbie,
Did you use the drawing HappyTot.dwg with the block Happy
with 2 attribute definitions?
Is at your local settings the decimal point separator a dot?
The macros in HappyBlok.xls are not really written in VBA,
but in Excel Dynamic Data Exchange (DDE) Macro language.
http://www.exceldde.com/
Some comment is made in Dutch.
Regards HofCAD CSI.
PS For 'Drawing a Helix Spiral in AutoCAD LT' or AutoCAD
with the Dynamic Data Exchange (DDE) method see:
http://forums.augi.com/showthread.php?t=112935
or http://www.theswamp.org/index.php?topic=31206.msg368008#msg368008
For 'Need lisp routine input a data in CAD' see:
http://forums.augi.com/showthread.php?t=98100&highlight=DDE-LINES.xls+hofcad
-
CAB & HofCAD,
I really appreciate your help. I got your codes to work.