Topic: Data to Excel

[MSTG007]

I found a lisp that can take polyline areas into a new excel session and file. I am trying to also extract out of the elevation with it but no luck as usual lol.

but my real question is to see if this possible once I can get all the data extracted.

I would like to know within a lisp if I can take the data extract it but with the cells next to the imported cell data have two formulas with it?

I know I can do this via excel VBA to AutoCAD, just the other way around I am not sure about.

So if I have a header of Elevation and Area then have the first formula next to the area "=IF(SUM(A2)=0,0,(A3-A2)*AVERAGE(B2:B3))" and the last one after that be "=IF(SUM(A2)=0,0,D2+C3)".

I guess even if I can get one formula next to it I think I could make it work.

Thanks for any input!!

Code: [Select]

(defun C:ax (/ *error* abks aexc area ar_data asht col en i obj row row_data ss xbks xcel xshs)

(vl-load-com)
 
(defun *error* (msg)
  (if
    (vl-position
      msg
      '("console break"
"Function cancelled"
"quit / exit abort"
)
      )
     (princ "Error!")
     (princ msg)
     )

  )
(if (setq ss (ssget (list (cons 0 "LWPOLYLINE")(cons 70 1))))

  (progn
    (setq i -1
  )
    (repeat (sslength ss)
      (setq en   (ssname ss (setq i (1+ i)))
    obj (vlax-ename->vla-object en)
    ar_data  (cons (setq area (vla-get-area obj)) ar_data)
      )
      )

    (setq ar_data (reverse ar_data))
    (alert "Close Excel File Only")
    (setq aexc (vlax-get-or-create-object "Excel.Application")
  xbks (vlax-get-property aexc "Workbooks")
  abks (vlax-invoke-method xbks "Add")
  xshs (vlax-get-property abks "Sheets")
  asht (vlax-get-property xshs "Item" 1)
  xcel (vlax-get-property asht "Cells")
  )
    (vla-put-visible aexc :vlax-true)
    (vlax-put-property aexc "UseSystemSeparators" :vlax-false)
    (vlax-put-property aexc "DecimalSeparator" (vlax-make-variant "." 8))           
    (setq row 0
  col 1
  )

    (vlax-put-property xcel  "NumberFormat"
      (vlax-make-variant "0.00" 8)
      )
   
    (repeat (length ar_data)
      (setq row_data (car ar_data))
      (setq row (1+ row))
      (vlax-put-property
xcel
"Item"
row
col
(vl-princ-to-string row_data)
)
      (setq ar_data (cdr ar_data))
      )

    (vlax-invoke-method
      abks
      'SaveAs
      "C:\\ImportAreas.xls"
      -4143
      nil
      nil
      :vlax-false
      :vlax-false
      1
      2
      )

    (vlax-release-object xcel)
    (vlax-release-object asht)
    (vlax-release-object xshs)
    (vlax-release-object abks)
    (vlax-release-object xbks)
    (vlax-release-object aexc)
    (setq aexc nil)
    (gc)
    (gc)
    )
  (*error* nil)
  )
  (princ)
  )
(prompt "\n\t\t>>>\tType AX to import areas to Excel\t<<<\n")
  (princ)


Source:
http://www.cadtutor.net/forum/showthread.php?41722-Closed-polyline-Area-values-to-Excel

[MSTG007]

Ok. I am going to make this so much easier I hope! Right now the way the lisp works it carries selected "polyline areas" to a new worksheet. I am trying to get it to add another column for the elevation data.

Any ideas what I did wrong? lol

Code: [Select]

(defun C:ax (/ *error* abks aexc area ar_data asht col en i obj row row_data ss xbks xcel xshs)

(vl-load-com)
 
(defun *error* (msg)
  (if
    (vl-position
      msg
      '("console break"
"Function cancelled"
"quit / exit abort"
)
      )
     (princ "Error!")
     (princ msg)
     )

  )
(if (setq ss (ssget (list (cons 0 "LWPOLYLINE")(cons 70 1))))

  (progn
    (setq i -1
  )
    (repeat (sslength ss)
      (setq en   (ssname ss (setq i (1+ i)))
    obj (vlax-ename->vla-object en)
    ar_data  (cons (setq area (vla-get-elevation obj)) ar_data)
      )
      (setq en   (ssname ss (setq i (2+ i)))
    obj (vlax-ename->vla-object en)
    ar_data2  (cons (setq area (vla-get-area obj)) ar_data2)
      )
      )

    (setq ar_data (reverse ar_data))
    (alert "Close Excel File Only")
    (setq aexc (vlax-get-or-create-object "Excel.Application")
  xbks (vlax-get-property aexc "Workbooks")
  abks (vlax-invoke-method xbks "Add")
  xshs (vlax-get-property abks "Sheets")
  asht (vlax-get-property xshs "Item" 1)
  xcel (vlax-get-property asht "Cells")
  )
    (vla-put-visible aexc :vlax-true)
    (vlax-put-property aexc "UseSystemSeparators" :vlax-false)
    (vlax-put-property aexc "DecimalSeparator" (vlax-make-variant "." 8))           
    (setq row 0
  col 1
  )

    (vlax-put-property xcel  "NumberFormat"
      (vlax-make-variant "0.00" 8)
      )
   
    (repeat (length ar_data)
      (setq row_data (car ar_data))
      (setq row (1+ row))
      (vlax-put-property
xcel
"Item"
row
col
(vl-princ-to-string row_data)
)
      (setq ar_data (cdr ar_data))
      )

    (repeat (length ar_data2)
      (setq row_data (car ar_data2))
      (setq row (2+ row))
      (vlax-put-property
xcel
"Item"
row
col
(vl-princ-to-string row_data)
)
      (setq ar_data2 (cdr ar_data2))
      )

    (vlax-invoke-method
      abks
      'SaveAs
      "C:\\ImportAreas.xls"
      -4143
      nil
      nil
      :vlax-false
      :vlax-false
      1
      2
      )

    (vlax-release-object xcel)
    (vlax-release-object asht)
    (vlax-release-object xshs)
    (vlax-release-object abks)
    (vlax-release-object xbks)
    (vlax-release-object aexc)
    (setq aexc nil)
    (gc)
    (gc)
    )
  (*error* nil)
  )
  (princ)
  )
(prompt "\n\t\t>>>\tType AX to import areas to Excel\t<<<\n")
  (princ)