Excel to Autocad Help me

Can I import with Autolisp coordinated with a given name in Excel and generate a block in Autocad identified with the name and placed at coordinate respectively?

Thank you for your help.

From the sounds of your request... yes. Though you will have to be a bit more descriptive about what you are trying to do.

I have a list with one name in one column and the x and y coordinates in two other columns in excel. My Autocad 2007 is the basic.In my office this version will be replaced by 2010. What I want is a lisp routine that will read data from excel file and place in a given block autocad position coordinates of each point with its name (the same name that is in the first column of excel) above or down the block in the drawing.

I have a list with one name in one column and the x and y coordinates in two other columns in excel. My Autocad 2007 is the basic.In my office this version will be replaced by 2010. What I want is a lisp routine that will read data from excel file and place in a given block autocad position coordinates of each point with its name (the same name that is in the first column of excel) above or down the block in the drawing.

See how it will works:

(vl-load-com)
;;local defun
(defun read_excel_range (FilePath ShtNum Address / ExcelApp ExcData Sht UsdRange Wbk)
(setq ExcelApp (vlax-get-or-create-object "Excel.Application"))
(vla-put-visible ExcelApp :vlax-true)
(vlax-put-property ExcelApp 'DisplayAlerts :vlax-true)
(setq Wbk (vl-catch-all-apply
    'vla-open
    (list (vlax-get-property ExcelApp "WorkBooks") FilePath)
    )
     )
(setq Sht (vl-catch-all-apply
    'vlax-get-property
    (list (vlax-get-property Wbk "Sheets")
   "Item"
   ShtNum
   )
    )
     )
(vlax-invoke-method Sht "Activate")
(setq UsdRange (vlax-get-property
  (vlax-get-property Sht 'Cells)
  "Range"
  Address)
     ExcData  (vlax-safearray->list
  (vlax-variant-value
    (vlax-get-property UsdRange 'Value2)
    )
  )
     )
(setq
 ExcData (mapcar
    (function (lambda (x) (mapcar 'vlax-variant-value x)))
    ExcData
    )
 )
(setq
 ExcData (vl-remove-if
    (function (lambda (x) (member nil x)))
    ExcData
    )
 )
(vl-catch-all-apply
 'vlax-invoke-method
 (list Wbk "Close")
 )
(vl-catch-all-apply
 'vlax-invoke-method
 (list ExcelApp "Quit")
 )
(mapcar
 (function
   (lambda (x)
     (vl-catch-all-apply
(function (lambda ()
     (progn
       (if (not (vlax-object-released-p x))
  (progn
    (vlax-release-object x)
    (setq x nil)
    )
  )
       )
     )
   )
)
     )
   )
 (list UsdRange Sht Wbk ExcelApp)
 )

 (gc)
 (gc)
 (gc)
 ExcData
)
;; main program
(defun C:XB (/ acsp address data_list fname sheetnum)

(setq fname (getfiled "Select Excel File:" (getvar "dwgprefix") "XLS" 4)
      sheetnum "Sheet1"
      address "A:C"
      )
(setq data_list (read_excel_range fname sheetnum address))
;; remove headers from list
(setq data_list (vl-remove-if
    (function (lambda (a) (not (numberp (cadr a)))))
    data_list))
(if data_list
  (progn
    (setq acsp (vla-get-block
   (vla-get-activelayout
     (vla-get-activedocument
       (vlax-get-acad-object)))))
    (foreach item  data_list
      (vlax-invoke-method
 acsp
 'InsertBlock
 (vlax-3d-point
   (list (cadr item) (caddr item) 0))
 (car item) 1 1 1 0)

      )
    )
  )
 (princ)
 )
(prompt
 "\n\t\t>>>\tType XB to insert block from Excel \t<<<")
(prin1)

~'J'~

See how it will works:

 

(vl-load-com)
;;local defun
(defun read_excel_range (FilePath ShtNum Address / ExcelApp ExcData Sht UsdRange Wbk)
(setq ExcelApp (vlax-get-or-create-object "Excel.Application"))
(vla-put-visible ExcelApp :vlax-true)
(vlax-put-property ExcelApp 'DisplayAlerts :vlax-true)
(setq Wbk (vl-catch-all-apply
    'vla-open
    (list (vlax-get-property ExcelApp "WorkBooks") FilePath)
    )
     )
(setq Sht (vl-catch-all-apply
    'vlax-get-property
    (list (vlax-get-property Wbk "Sheets")
   "Item"
   ShtNum
   )
    )
     )
(vlax-invoke-method Sht "Activate")
(setq UsdRange (vlax-get-property
  (vlax-get-property Sht 'Cells)
  "Range"
  Address)
     ExcData  (vlax-safearray->list
  (vlax-variant-value
    (vlax-get-property UsdRange 'Value2)
    )
  )
     )
(setq
 ExcData (mapcar
    (function (lambda (x) (mapcar 'vlax-variant-value x)))
    ExcData
    )
 )
(setq
 ExcData (vl-remove-if
    (function (lambda (x) (member nil x)))
    ExcData
    )
 )
(vl-catch-all-apply
 'vlax-invoke-method
 (list Wbk "Close")
 )
(vl-catch-all-apply
 'vlax-invoke-method
 (list ExcelApp "Quit")
 )
(mapcar
 (function
   (lambda (x)
     (vl-catch-all-apply
(function (lambda ()
     (progn
       (if (not (vlax-object-released-p x))
  (progn
    (vlax-release-object x)
    (setq x nil)
    )
  )
       )
     )
   )
)
     )
   )
 (list UsdRange Sht Wbk ExcelApp)
 )

 (gc)
 (gc)
 (gc)
 ExcData
)
;; main program
(defun C:XB (/ acsp address data_list fname sheetnum)

(setq fname (getfiled "Select Excel File:" (getvar "dwgprefix") "XLS" 4)
      sheetnum "Sheet1"
      address "A:C"
      )
(setq data_list (read_excel_range fname sheetnum address))
;; remove headers from list
(setq data_list (vl-remove-if
    (function (lambda (a) (not (numberp (cadr a)))))
    data_list))
(if data_list
  (progn
    (setq acsp (vla-get-block
   (vla-get-activelayout
     (vla-get-activedocument
       (vlax-get-acad-object)))))
    (foreach item  data_list
      (vlax-invoke-method
 acsp
 'InsertBlock
 (vlax-3d-point
   (list (cadr item) (caddr item) 0))
 (car item) 1 1 1 0)

      )
    )
  )
 (princ)
 )
(prompt
 "\n\t\t>>>\tType XB to insert block from Excel \t<<<")
(prin1)

 

~'J'~

fixo:

Don't know why, but this xb.lsp only works to insert the blocks at the coordinates listed in the Excel file if the blocks are already in the dwg.

Don't know why xb.lsp doesn't find the blocks that are in the acad path initially.

S

It opens excel but does nothing. I do not know if I have to create a range of 3 columns in Excel and if I have to give any name to the sheet. on the other hand I do not know if I have to create a block in autocad and what name should I give this block.

Thanks for the quick response because I really need this routine.

Best regards.

It opens excel but does nothing. I do not know if I have to create a range of 3 columns in Excel and if I have to give any name to the sheet. on the other hand I do not know if I have to create a block in autocad and what name should I give this block.

Thanks for the quick response because I really need this routine.

Best regards.

It opens excel but does nothing. I do not know if I have to create a range of 3 columns in Excel and if I have to give any name to the sheet. on the other hand I do not know if I have to create a block in autocad and what name should I give this block.

Thanks for the quick response because I really need this routine.

Best regards.

Hi

This lisp is working for years for me starting from A2005 to 2009

There are a lot of possible reasons bywich it's not working on your end

Upload sample drawing here with explanation for the best understanding your issue

~'J'~

Hi

This lisp is working for years for me starting from A2005 to 2009

There are a lot of possible reasons bywich it's not working on your end

Upload sample drawing here with explanation for the best understanding your issue

 

~'J'~

I sent you a dwg with a block called PA (circle with a triangle inside). Also send you the xls file with the coordinates. What I needed was the routine reads all lines of the excel file and put the design in each block coordinates the PA under the name of each written down as dwg present in a sample point of the third row of the xls file.

Note:The error message that appears is:

; error: bad argument type: VLA-OBJECT #

pardon for my bad english

Regards.

Test.zip

I sent you a dwg with a block called PA (circle with a triangle inside). Also send you the xls file with the coordinates. What I needed was the routine reads all lines of the excel file and put the design in each block coordinates the PA under the name of each written down as dwg present in a sample point of the third row of the xls file.

 

Note:The error message that appears is:

 

; error: bad argument type: VLA-OBJECT #

 

pardon for my bad english

 

Regards.

I can't help you right now, some vey own work is here

I will be back later

~'J'~

I sent you a dwg with a block called PA (circle with a triangle inside)...

I could not open your drawing

Here is a code that was edited to your suit,

it will draw points with text only

What is the tag name of your block PA?

Easily to rewrite it to insert blocks and change attributes

value by Excel data

(vl-load-com)
;;local defun
(defun read_excel_range (FilePath ShtNum Address / ExcelApp ExcData Sht UsdRange Wbk)
(setq ExcelApp (vlax-get-or-create-object "Excel.Application"))
(vla-put-visible ExcelApp :vlax-true)
(vlax-put-property ExcelApp 'DisplayAlerts :vlax-true)
(setq Wbk (vl-catch-all-apply
    'vla-open
    (list (vlax-get-property ExcelApp "WorkBooks") FilePath)
    )
     )
(setq Sht (vl-catch-all-apply
    'vlax-get-property
    (list (vlax-get-property Wbk "Sheets")
   "Item"
   ShtNum
   )
    )
     )
(vlax-invoke-method Sht "Activate")
(setq UsdRange (vlax-get-property
  (vlax-get-property Sht 'Cells)
  "Range"
  Address)
     ExcData  (vlax-safearray->list
  (vlax-variant-value
    (vlax-get-property UsdRange 'Value2)
    )
  )
     )
(setq
 ExcData (mapcar
    (function (lambda (x) (mapcar 'vlax-variant-value x)))
    ExcData
    )
 )
(setq
 ExcData (vl-remove-if
    (function (lambda (x) (member nil x)))
    ExcData
    )
 )
(vl-catch-all-apply
 'vlax-invoke-method
 (list Wbk "Close")
 )
(vl-catch-all-apply
 'vlax-invoke-method
 (list ExcelApp "Quit")
 )
(mapcar
 (function
   (lambda (x)
     (vl-catch-all-apply
(function (lambda ()
     (progn
       (if (not (vlax-object-released-p x))
  (progn
    (vlax-release-object x)
    (setq x nil)
    )
  )
       )
     )
   )
)
     )
   )
 (list UsdRange Sht Wbk ExcelApp)
 )

 (gc)
 (gc)
 (gc)
 ExcData
)
;; main program
(defun C:Xpoints (/ acsp address data_list fname inspoint sheetnum textheight)
(setvar "pdmode" 34)
(setvar "pdsize" 2)
(if (and
(setq fname (getfiled "Select Excel File:" (getvar "dwgprefix") "XLS" 4)
      )
(setq sheetnum (getstring T "\nEnter a sheet name: ")
      address "A:C"
      )
)
 (progn
(if (zerop (setq textheight (getvar "textsize")))
  (progn
    (initget 7)
    (setq textheight "\nSpecify text height: "))
  )
;;get data
(setq data_list (read_excel_range fname sheetnum address))
;; remove headers from list
(setq data_list (vl-remove-if
    (function (lambda (a) (not (numberp (cadr a)))))
    data_list))
(if data_list
  (progn
    (setq acsp (vla-get-block
   (vla-get-activelayout
     (vla-get-activedocument
       (vlax-get-acad-object)))))
    (foreach item  data_list
      (vlax-invoke-method
 acsp
 'AddPoint
 (vlax-3d-point
   (setq inspoint (list (cadr item) (caddr item) 0)))
 )
      (setq textobj (vlax-invoke-method
 acsp
 'AddText
 (vl-princ-to-string (car item))
 (setq textpoint (vlax-3d-point
   (mapcar '+ (list 0 (/ textheight 2) 0) inspoint)))
 textheight
 )
     )
      (vlax-put-property textobj 'Alignment acAlignmentBottomCenter)
      (vlax-put-property textobj 'TextAlignmentPoint textpoint)
      )
    )
  )
(vla-zoomcenter (vlax-get-acad-object) textpoint (* textheight 12))
)
  )
 (princ)
 )
(prompt
 "\n\t\t>>>\tType XPOINTS or xpoints to execute\t<<<")
(prin1)

Ok, just a shot in dark

Change the block name and tag to suit

(vl-load-com)
;;local defun
(defun read_excel_range (FilePath ShtNum Address / ExcelApp ExcData Sht UsdRange Wbk)
(setq ExcelApp (vlax-get-or-create-object "Excel.Application"))
(vla-put-visible ExcelApp :vlax-true)
(vlax-put-property ExcelApp 'DisplayAlerts :vlax-true)
(setq Wbk (vl-catch-all-apply
    'vla-open
    (list (vlax-get-property ExcelApp "WorkBooks") FilePath)
    )
     )
(setq Sht (vl-catch-all-apply
    'vlax-get-property
    (list (vlax-get-property Wbk "Sheets")
   "Item"
   ShtNum
   )
    )
     )
(vlax-invoke-method Sht "Activate")
(setq UsdRange (vlax-get-property
  (vlax-get-property Sht 'Cells)
  "Range"
  Address)
     ExcData  (vlax-safearray->list
  (vlax-variant-value
    (vlax-get-property UsdRange 'Value2)
    )
  )
     )
(setq
 ExcData (mapcar
    (function (lambda (x) (mapcar 'vlax-variant-value x)))
    ExcData
    )
 )
(setq
 ExcData (vl-remove-if
    (function (lambda (x) (member nil x)))
    ExcData
    )
 )
(vl-catch-all-apply
 'vlax-invoke-method
 (list Wbk "Close")
 )
(vl-catch-all-apply
 'vlax-invoke-method
 (list ExcelApp "Quit")
 )
(mapcar
 (function
   (lambda (x)
     (vl-catch-all-apply
(function (lambda ()
     (progn
       (if (not (vlax-object-released-p x))
  (progn
    (vlax-release-object x)
    (setq x nil)
    )
  )
       )
     )
   )
)
     )
   )
 (list UsdRange Sht Wbk ExcelApp)
 )

 (gc)
 (gc)
 (gc)
 ExcData
)
;; main program
(defun C:XBLK (/ acsp address attcoll blkname blkobj data_list
    fname inspoint sheetnum tag textheight)
(setvar "pdmode" 34)
(setvar "pdsize" 2)
(if (and
(setq fname (getfiled "Select Excel File:" (getvar "dwgprefix") "XLS" 4)
      )
(setq sheetnum (getstring T "\nEnter a sheet name: ")
      address "A:C"
      )
)
 (progn
(if (zerop (setq textheight (getvar "textsize")))
  (progn
    (initget 7)
    (setq textheight "\nSpecify text height: "))
  )

(setq blkname (getstring T "\nEnter block name <PA>: "))
(if (eq "" blkname)(setq blkname "PA"))
(if (not (tblsearch "block" blkname))
  (progn
    (alert (strcat "Block " blkname " does not exist. Exit"))
    (exit)(princ)
    )
  )
(setq tag (getstring T "\nEnter tag <NUMBER>: "))
(if (eq "" tag)(setq tag "NUMBER"))
;;get data from Excel
(setq data_list (read_excel_range fname sheetnum address))
;; remove headers from list
(setq data_list (vl-remove-if
    (function (lambda (a) (not (numberp (cadr a)))))
    data_list))
(if data_list
  (progn
    (setq acsp (vla-get-block
   (vla-get-activelayout
     (vla-get-activedocument
       (vlax-get-acad-object)))))
    (foreach item  data_list

      (setq blkobj (vlax-invoke-method
 acsp
 'InsertBlock

   (setq inspoint (vlax-3d-point(list (cadr item) (caddr item) 0)))
 blkname
  1. 1. 1. 0.
 )
     )

      (setq attcoll (vlax-invoke blkobj 'GetAttributes))
      (foreach attobj attcoll
 (if (eq tag (vla-get-tagstring attobj))
   (vla-put-textstring attobj (vl-princ-to-string (car item))
     )
   )
 )
      )
    )
  )
(vla-zoomextents (vlax-get-acad-object))
)
  )
 (princ)
 )
(prompt
 "\n\t\t>>>\tType XBLK or xblk to execute\t<<<")
(prin1)

~'J'~

Can we take this one step further? I have a similar need for inserting groundwater exploration locations from a field data sheet into a site drawing. Excel data setup with 5 columns, left to right - Block name, Xcoord, Ycoord, Well Name and Elev. Final output on drawing would be block inserted at x,y coord, with well name (block attribute = ID) and Groundwater Elevation (block attribute = EL).

Appreciate it!

Can we take this one step further? I have a similar need for inserting groundwater exploration locations from a field data sheet into a site drawing. Excel data setup with 5 columns, left to right - Block name, Xcoord, Ycoord, Well Name and Elev. Final output on drawing would be block inserted at x,y coord, with well name (block attribute = ID) and Groundwater Elevation (block attribute = EL).

 

Appreciate it!

Upload here your drawing with one inserted block for example

But I can't do it right now, still watching world cup

~'J'~

Upload here your drawing with one inserted block for example

But I can't do it right now, still watching world cup

 

~'J'~

Thanks ~'J'~! I'll upload tomorrow morning. We're all leaving office to catch the second half of the match also! Cheers!

Thanks ~'J'~! I'll upload tomorrow morning. We're all leaving office to catch the second half of the match also! Cheers!

Hope this one will be satisfy your needs

(vl-load-com)
;;local defun
(defun read_excel_range (FilePath ShtNum Address / ExcelApp ExcData Sht UsdRange Wbk)
(setq ExcelApp (vlax-get-or-create-object "Excel.Application"))
(vla-put-visible ExcelApp :vlax-true)
(vlax-put-property ExcelApp 'DisplayAlerts :vlax-true)
(setq Wbk (vl-catch-all-apply
    'vla-open
    (list (vlax-get-property ExcelApp "WorkBooks") FilePath)
    )
     )
(setq Sht (vl-catch-all-apply
    'vlax-get-property 
    (list (vlax-get-property Wbk "Sheets")
   "Item"
   ShtNum
   )
    )
     )
(vlax-invoke-method Sht "Activate")
(setq UsdRange (vlax-get-property
  (vlax-get-property Sht 'Cells)
  "Range"
  Address)
     ExcData  (vlax-safearray->list
  (vlax-variant-value
    (vlax-get-property UsdRange 'Value2)
    )
  )
     )
(setq
 ExcData (mapcar
    (function (lambda (x) (mapcar 'vlax-variant-value x)))
    ExcData
    )
 )
(setq
 ExcData (vl-remove-if
    (function (lambda (x) (member nil x)))
    ExcData
    )
 )
(vl-catch-all-apply
 'vlax-invoke-method
 (list Wbk "Close")
 )
(vl-catch-all-apply
 'vlax-invoke-method
 (list ExcelApp "Quit")
 )
(mapcar
 (function
   (lambda (x)
     (vl-catch-all-apply
(function (lambda ()
     (progn
       (if (not (vlax-object-released-p x))
  (progn
    (vlax-release-object x)
    (setq x nil)
    )
  )
       )
     )
   )
)
     )
   )
 (list UsdRange Sht Wbk ExcelApp)
 )

 (gc)
 (gc)
 (gc)
 ExcData
)
;; main program
(defun C:BEL (/ acsp address attcoll blkname blkobj data_list fname inspoint sheetnum)
(setvar "pdmode" 34)
(setvar "pdsize" 2)
(if (and
(setq fname (getfiled "Select Excel File:" (getvar "dwgprefix") "XLS" 4)
      )
(setq sheetnum (getstring T "\nEnter a sheet name: ")
      address "A1:E1000" ;<--5 columns
      )
)
 (progn
(setq data_list (read_excel_range fname sheetnum address))
;; remove headers from list
(setq data_list (vl-remove-if
    (function (lambda (a) (not (numberp (cadr a)))))
    data_list))
(if data_list
  (progn
    (setq acsp (vla-get-block
   (vla-get-activelayout
     (vla-get-activedocument
       (vlax-get-acad-object)))))
    (foreach item  data_list

      (setq blkobj (vlax-invoke-method
 acsp
 'InsertBlock 
  (setq inspoint (vlax-3d-point(list (cadr item) (caddr item) (last item))))
 (car item)
  1. 1. 1. 0.
 )
     )

      (setq attcoll (vlax-invoke blkobj 'GetAttributes))

      (foreach attobj attcoll
 (cond ((eq "ID" (vla-get-tagstring attobj))
   (vla-put-textstring attobj (vl-princ-to-string (nth 3 item))
     )
   )
       ((eq "EL" (vla-get-tagstring attobj))
   (vla-put-textstring attobj (vl-princ-to-string (nth 4 item))
     )
   )
  )
 )
      )
    )
  )
(vla-zoomextents (vlax-get-acad-object))
)
  )
 (princ)
 )
(prompt
 "\n\t\t>>>\tType BEL or bel to execute\t<<<")
(prin1)

~'J'~

Good morning Fixo,

Greatly appreciate your help! When I tried your program this morning I got a malformed error message:

Command: appload

Insert_Exploration.lsp successfully loaded.

Command: ; error: malformed list on input

I have attached the test files as you requested yesterday.

thanks again - KAC

EXPLORATION LISP TEST.zip

Good morning Fixo,

 

Greatly appreciate your help! When I tried your program this morning I got a malformed error message:

 

Command: appload

Insert_Exploration.lsp successfully loaded.

Command: ; error: malformed list on input

 

I have attached the test files as you requested yesterday.

 

thanks again - KAC

hi KAC,

I tested fixo's code with you dwg and xls because I am also interested in what he wrote for you. Looks like it should work. It worked for me. All I did was cut and paste. You may have an extra or missing closing opening parenthesis. Maybe when you cut and paste you missed out one.

Yeah, must have mised something, because I just tried again and it works great!!

THANK YOU FIXO!! I truely appreciate all your help!

KC

hi KAC,

 

I tested fixo's code with you dwg and xls because I am also interested in what he wrote for you. Looks like it should work. It worked for me. All I did was cut and paste. You may have an extra or missing closing opening parenthesis. Maybe when you cut and paste you missed out one.

Thanks, ziko

Welcome on board

~'J'~

Yeah, must have mised something, because I just tried again and it works great!!

 

THANK YOU FIXO!! I truely appreciate all your help!

 

KC

Glad it that works

And also, please, create the new threads separately for

even of your questions

Btw, this is a huge size work what you want I hope somebody else

from this forum coulde be able to solve your task

~'J'~