Improve Lisp to Save Exported Sheet

[RenatoND]

Hi Guys,

 

I have an AutoLISP that creates an Excel sheet from selected table in an AutoCAD Drawing. It exports tables formed by lines and texts, also recognizing what should be merged cells. It opens a new Excel spreadsheet and fill the respective cells. Works like a charm for the job I need to do.

 

However, It does have a flaw. It doesn't saves the generated sheet. I have to do it manually. Can you guys please help-me to complete this LISP? I Have zero programming knowledge.

What I'd like it to do is to save the generated spreadsheet in the same folder as the autocad file, with the same name as the autocad file. Lilke DWG001.DWG would save a spreadsheet called DWG001.xls

 

I'll attach my current AutoLISP file.

 

Thanks in advance, guys.

TE_TableExport_Exporta tabelas com linhas e textos para Excel.lsp

[BIGAL]

This is not the answer as have to search into your code for correct variable name but rather method its from getexcel.lsp

 

; CloseExcel - Closes Excel spreadsheet
; Arguments: 1
;   ExcelFile$ = Excel saveas filename or nil to close without saving
; Syntax examples:
; (CloseExcel "C:\\Temp\\Temp.xls") = Saveas C:\Temp\Temp.xls and close
; (CloseExcel nil) = Close without saving

GetExcel.lsp

 

setq	xlapp	   (vlax-get-or-create-object "Excel.Application");(fixo)
	xlbooks  (vlax-get-property xlapp 'Workbooks)
	xlbook	   (vlax-invoke-method xlbooks 'Add)
	xlsheets (vlax-get-property xlbook 'Sheets)
	xlsheet	   (vlax-get-property xlsheets 'Item 1)
	xlcells	   (vlax-get-property xlsheet 'Cells)
	)

 

Edited November 2, 2019 by BIGAL

[rlx]

haven't run or tested  the code but I expect it to be something like this :

.

.

.

(setq hmergelist2 (convertlist hmergelist)
      vmergelist2 (convertlist vmergelist))
(applylist hmergelist2)
(applylist vmergelist2)
 (vlax-invoke-method
   (vlax-get-property xlsheet 'Columns)
   'AutoFit)
;;;  align all columns to center
   (vlax-put-property
     (setq urange(vlax-get-property xlsheet 'UsedRange))
   'HorizontalAlignment -4108)
;;;  draw grid lines
  (setgridlines xlapp urange)

 

;;;*********** save your file here before releasing excel ********************
;;; filename can be like : (setq your-filename (strcat (getvar 'dwgprefix) (vl-filename-base (getvar 'dwgname)) ".xls"))
(vlax-invoke-method xlbook 'SaveAs your-filename -4143 nil nil :vlax-false :vlax-false 1 2)

;;;***************************************************************************


 (mapcar '(lambda (x);(fixo)
         (vl-catch-all-apply
           '(lambda    ()
          (vlax-release-object x)
        )
         )
       )
      (list xlcells xlsheet xlsheets xlbook xlbooks xlapp)
  )
  (setq  xlapp nil)
  (gc)(gc)(gc)
(mapcar '(lambda (x)(and x (not (vlax-object-released-p x))(vlax-release-object x))) (list ssitem))
(mapcar '(lambda (txt)(redraw txt 1)) textlist);;using redraw function again
(setq *error* oerror)
(vla-EndUndoMark ActDoc)
(princ)
(princ)
);defun

.

.

.