Topic: (Excel to AutoCAD) and (AutoCAD to Excel)

[terrycadd]

I’ve been working on a general purpose function for (Excel to AutoCAD) and (AutoCAD to Excel) applications.  I owe a lot of credit to Gile here at the Swamp for his AutoLISP functions ColumnRow, Alpha2Number and Number2Alpha.  In this version I did away with the VBA concept and rewrote it using Vlisp functions instead. 

For you beta testers, here’s how easy it works.

[The Get Part]
GetExcel - Stores the values from an Excel spreadsheet into *ExcelData@ list
___Syntax:  (GetExcel ExcelFile$ SheetName$ MaxRange$)
___Example: (GetExcel "C:\\Folder\\Filename.xls" "Sheet1" "L30")
GetCell - Returns the cell value from the *ExcelData@ list
___Syntax:  (GetCell Cell$)
___Example: (GetCell "H15")

[The Put Part]
OpenExcel - Opens an Excel spreadsheet
___Syntax:  (OpenExcel ExcelFile$ SheetName$ Visible)
___Example: (OpenExcel "C:\\Folder\\Filename.xls" "Sheet1" nil)
PutCell - Put values into Excel cells
___Syntax:  (PutCell StartCell$ Data$) or (PutCell StartCell$ DataList@)
___Example: (PutCell "A1" (list "GP093" 58.5 17 "Base" "3'-6 1/4\""))
CloseExcel - Closes Excel session
___Syntax:  (CloseExcel ExcelFile$)
___Example: (CloseExcel "C:\\Folder\\Filename.xls")

Also, CAB if you’re out there; remember the thread discussion on getting my DrawExcel.lsp function to work on your system?  I revised it also with these functions.  It’s out there on the AutoLISP Exchange @ http://web2.airmail.net/terrycad.

Upcoming Bull Frog.

[Adesu]

Hi Terry Cadd,
First said "Thanks you very much for your code", and I just test with my code, but still problem, it said

Code: [Select]

; error: bad argument type: VLA-OBJECT nil


Code: [Select]

(defun c:wsie (/ )
  (setq ExcelFile$ "D:\\YBI\\Test with Autolisp.xls")
  (setq SheetName$ "Sheet1")
  (setq MaxRange$ "L30")
  (setq Num# 731)
  (GetExcel ExcelFile$ SheetName$ MaxRange$)
  (setq Cell$ "A1")
  (GetCell Cell$)
  (setq Str$ "ABC")
  (Alpha2Number Str$)
  (setq StartCell$ "A1")
  (setq Data@ "PART NUMBER")
  (PutCell StartCell$ Data@)
  (CloseExcel ExcelFile$)
  (princ)
  )  ; defun


[terrycadd]

Adesu,
Good morning Bull Frog. Thought I was going to croak for a second when I read your post.  This should clear things up.

Code: [Select]

(defun c:The_Get_Part (/ )
  (setq ExcelFile$ "C:\\Temp\\Test with Autolisp.xls")
  (setq SheetName$ "Sheet1")
  (setq MaxRange$ "L30")
  (GetExcel ExcelFile$ SheetName$ MaxRange$)
  (setq Cell$ "A1")
  (GetCell Cell$);Or you can just use the global *ExcelData@ list
);defun
(defun c:The_Put_Part (/ )
  (setq ExcelFile$ "C:\\Temp\\Test with Autolisp.xls")
  (setq SheetName$ "Sheet1")
  (setq StartCell$ "A1")
  (setq Data@ "PART NUMBER");Or you may use a list here
  ;To open a new spreadsheet use (OpenExcel nil SheetName$ nil)
  (OpenExcel ExcelFile$ SheetName$ nil)
  (PutCell StartCell$ Data@)
  (CloseExcel ExcelFile$)
  (princ)
);defun


[CAB]

Thanks Terry, I'll try it when I get some free time.
Note that the DrawExcel.dvb file link is not working.

<spelling>

[terrycadd]

CAB,
Thanks for pointing that out. I revised DrawExcel this past week using only visual lisp expressions as some people were having problems with the dvb file and their version of AutoCAD, so I abandoned the whole idea. The new version is more efficient and doesn't need the dvb file. Thanks again. I just updated that section on the website.

Terry

P.S. The file GetExcel.lsp is now part of DrawExcel.lsp. That's what I used to replace the dvb file with.

FYI: Adesu made a good point that I wasn't too clear as to how to use the functions in GetExcel.lsp, so the new version on the website has better documentation at the top of the file with examples. The code below that, is still the same as originally posted in this thread.

[Adesu]

Hi Terry Cadd,
It's great code, thanks for your help.
here my code

Code: [Select]

; cte = cad to excel
(defun c:cte (/ cnt data1 data2 data3 data4 data5 data6
      data_1in data_2in data_3in data_4in data_5in data_6in
      excelfile$ sheetname$)
  (setq ExcelFile$ "C:\\Temp\\Test with Autolisp.xls")
  (setq SheetName$ "Sheet1")
  (setq data1 '("A1" "A2" "A3" "A4" "A5" "A6"))
  (setq data2 '("No." "1" "2" "3" "4" "5"))
  (setq data3 '("B1" "B2" "B3" "B4" "B5" "B6"))
  (setq data4 '("NAME" "Bolt" "Nut" "Ring" "Nail" "Gear"))
  (setq data5 '("C1" "C2" "C3" "C4" "C5" "C6"))
  (setq data6 '("SIZE" "M10" "M5" "M12" "5 cm" "Small Gear"))   
  (OpenExcel ExcelFile$ SheetName$ nil)
  (setq cnt 0)
  (repeat
    6
    (setq data_1in (nth cnt data1))
    (setq data_2in (nth cnt data2))
    (PutCell data_1in data_2in)
    (setq data_3in (nth cnt data3))
    (setq data_4in (nth cnt data4))
    (PutCell data_3in data_4in)
    (setq data_5in (nth cnt data5))
    (setq data_6in (nth cnt data6))
    (PutCell data_5in data_6in)   
    (setq cnt (1+ cnt))
    )  ; repeat
  (CloseExcel ExcelFile$)
  (princ)
);defun


[terrycadd]

See that was fairly easy. I have been doing the PutCell section slightly different, but there are many ways to accomplish the same thing. Thanks for your feedback.
Terry

Code: [Select]

(setq Index# 1)
(repeat 6
  (setq Cell-ID$ (strcat “A” (itoa Index#)))
  (PutCell Cell-ID$ (nth (1- Index#) All-Data@))
  (setq Index# (1+ Index#))
);repeat