Export custom table to Excel using AutoLisp

[cncah]

Hello, I found a Lisp program online that takes a table and exports it to an open Excel workbook. I'm not familiar with the vla and vlax commands, so I was wondering if I could get some help in editing it. Currently, it takes all values of the table and exports them. I'm just needing the header value and column C from C:4 all the way down. Any help would be appreciated. Thank you.

[smaher12]

You can right click on the table and export the table in .csv format. Open the .csv file in Excel and then save the file in .xls format.

[cncah]

I know about the TABLEEXPORT command but, I'm needing to have the process completely automated from a stand-alone program in vb.net. I was able to limp through and connect to Autocad with vb.net and send basic commands but I don't know any of the vb commands for Autocad. I've done some basic AutoLisp programming, but most of it is based on knowing Autocad command line actions. I only know a couple of vla and vlax commands in AutoLisp. What I was going to try and do from my outside windows form program was prompt the user to pick an Autocad file to open, open it, run an AutoLisp program to export the table(the table is on it's own layer called BOMTable), then close Autocad leaving the excel document open, and continue on with the rest of my program. I've been tasked with writing a program that will span 4 different softwares and will run without user input completely. Aside from the information I gather from them before they hit the start button on the windows form of course.

[kabeel.2016.eng]

what's shortcut of this lisp

[lee.hamilton98KWN]

tbl2ex is the command, however I don't think this works with 2020 and on...

[nithin_john8MMB5]

its not working with 2022

[Sea-Haven]

If you run from within VLIDE what error occurs just saying does not run does not help to make it work.

[nithin_john8MMB5]

I'm looking for customized (manually generated) table export to Excell sheet, right now i feel this lisp only working as autocad table can export to excel.

 

[Sea-Haven]

Try this, as in code do not have excel open.

; simple table to excel
; expects Title header and data
; BY Alanh Jan 2022
; do not have excel open

(defun c:tab2excel ( /  x y z AH:putcell Ah:opennew number2alpha obj cols row)

(defun AH:putcell (cellname val1 / )
(setq myRange (vlax-get-property  (vlax-get-property myxl "ActiveSheet") "Range" cellname))
(vlax-put-property myRange 'Value2 val1)
)

(defun AH:opennew ( /  )
(if (= (setq myxl (vlax-get-object "Excel.Application") ) nil)
(setq myxl (vlax-get-or-create-object "excel.Application"))
)
(vla-put-visible myXL :vlax-true)
(vlax-put-property myxl 'ScreenUpdating :vlax-true)
(vlax-put-property myXL 'DisplayAlerts :vlax-true)
)

; Number2Alpha - Converts Number into Alpha string
; Function By: Gilles Chanteau from Marseille, France
; Arguments: 1
;   Num# = Number to convert
; Syntax example: (Number2Alpha 731) = "ABC"
;-------------------------------------------------------------------------------
(defun Number2Alpha (Num# / Val#)
  (if (< Num# 27)
    (chr (+ 64 Num#))
    (if (= 0 (setq Val# (rem Num# 26)))
      (strcat (Number2Alpha (1- (/ Num# 26))) "Z")
      (strcat (Number2Alpha (/ Num# 26)) (chr (+ 64 Val#)))
    )
  )
);defun Number2Alpha

(AH:opennew)
(vlax-invoke-method (vlax-get-property myXL 'WorkBooks) 'Add)

(setq obj (vlax-ename->vla-object (car  (entsel "Pick table "))))
(setq cols (vla-get-columns obj))
(setq rows (vla-get-rows obj))
(alert (strcat (rtos rows 2 0) " rows " (rtos cols 2 0) " columns \n will now send to excel "))

(if (not AH:Toggs)(load "Multiple toggles.lsp"))
(setq ans (reverse (ah:toggs   '("Please choose " "Title " "Heading " "Data"))))


(If (= "1" (nth 0 ans))
(AH:putcell "A1" (vla-getText Obj 0 0 ))
)

(if (= "1" (nth 1 ans))
(progn
(setq x 1 y 1 z 2)
(repeat cols
(AH:putcell (strcat (Number2Alpha x) (rtos z 2 0)) (vla-getText Obj y (- x 1)  ))
(setq x (1+ x))
)
)
)

(if (= "1" (nth 2 ans))
(progn
(setq y 3)
(repeat (- rows 2)
(setq x 1 )
(repeat cols
(AH:putcell (strcat (Number2Alpha x) (rtos y 2 0)) (vla-getText Obj y (- x 1)  ))
(setq x (1+ x))
(princ (strcat "\nRow " (rtos y 2 0)))
)
(setq y (1+ y))
)
)
)

(vlax-release-object myXL)
(alert "Please save the excel and close if you want to import another table ")

(princ)
)
(c:tab2excel)

[Gobel_A]

The above code works good, but with some errors which includes first or second data row to be not exported depending on existing of title or/and header of table.

If the table contains only data rows, all rows are exported, but in my two columns table, the second column of first row is not exported.

If the table contains title and header, FIRST data row is not exported

If the table contains only header, SECOND data row is not exported

If the table contains only titel, SECOND data row is not exported

This behaviour is making this code useless because I never know if I can trust the exported table. 

Does anybody know hot to repair this lisp to be error free ? 

[Sea-Haven]

Are you talking about my code you could have PM me, need samples in a dwg then can test properly and not go back and forth.

[Gobel_A]

The sample table is attached. See also image for result.

[Sea-Haven]

One thing you can do and will need to change code enter row to start from, you can also pick a cell to get row to start at.

Will try to find some time been a while since I looked at it.

[Gobel_A]

It is not neccessary to pick a exact cell to start from. Just export of complete table with title and headers and all data rows will be enough. In actual code the first data row is not exported to excel, although the lisp show that will export the correct number of rows. 

[Sea-Haven]

Its on my to do list,it may be that Cell 0,0 the title is a merged cell so there is only 1 column as the cells are merged. I look for cells in all columns but there is no say 0 3 cell in the title.

I may do it this way

[Sea-Haven]

Message 9 code is updated, needs this as well save in a suport directory as its autoloaded when program ran.

[tanbqtb03]

My errors is same same, please waiting for fix version lisp

[Sea-Haven]

What is error I tried it again today and it seems to work, did you re-download post 9 the code is updated you will need Multi toggles as well.

Its not much good just saying its errors, need a description of error or images etc.

[Gobel_A]

I can confirm that the problem with the first row of data still persists. In addition, there is a small typo where the new lisp from post 9 is calling Multiple toggles.lsp, but the downloaded file from post nr.16 is named Multi toggles.lsp (Multiple/Multi). This was an easy fix. However, it still does not export the first data line.

Tested on both the table from message 12 and a randomly selected one, see image, and the first row is still missing in the export.

[Gobel_A]

I probably found it... see picture. This modification works for me and my table. The highlighted code has been modified. Modified lsp attached (including typo correction for Multi toggle.lsp) - all credit goes to the original author. Not tried across many tables, but I tried different tables and it worked fine this way.

@Sea-Haven