Create a new excel sheet and activate that sheet to write data through AutoLISP

[ShricharanaB]

Hi, 

I want to write some data to several sheets in a new excel file. So far I can create a new excel file, write data to the first sheet in that excel file, save and close. How ever I can't find a way to create a new sheet in that excel file and then I be able to switch to that sheet and write data to that sheet. 

Ex: I have several of rectangle blocks, circle blocks and triangle blocks in the drawing. I want to write say the sizes of the rectangle blocks in the first sheet, of circle blocks in second sheet and triangle blocks on third sheet.

How can I do that? 

[Sea-Haven · Accepted solution]

Thanks to fixo it is possible, here is code you should be able to work out how its put together cut from a bigger program different functions, get/put cell, get range, open/close/quit and so on.

; (setq myxl (vlax-get-or-create-object "excel.Application"))
; (defun openexcel-sheetname (filename sheetname / )
;(setq myBook (vl-catch-all-apply 'vla-open (list (vlax-get-property myXL "WorkBooks") fileName)))
;(setq mySheet (vl-catch-all-apply 'vlax-get-property (list (vlax-get-property myBook "Sheets") "Item" sheetName)))
;(vlax-invoke-method mySheet "Activate")
;)

; add a sheetName by FIXO no longer with us
(defun addsheet ()
(setq  xlSheets (vlax-get-property myBook "Sheets"))
;; add new sheet
(vlax-invoke-method xlsheets 'Add nil nil nil)
;; get the number of sheets
(setq xlCount (vlax-get-property xlSheets 'Count))
;; get the newly created sheet
(setq xlSheet (vlax-get-property xlSheets "Item" (vlax-get-property (vlax-get-property myXL 'ActiveSheet) 'Name)))
;; change name of sheet to what you need
(vlax-put-property xlSheet 'Name "NewSheetName")
;; move sheet to the end of book
(vlax-invoke-method xlSheet 'Move
(vlax-get-property xlSheets "Item"
(vlax-get-property xlSheets 'Count)))
)

how its put toge 

[ShricharanaB]

Hi, 

Thanks for the code! I was able to implement it in my code. 

However, it opens up the 'replace existing file?' window if a file in that name already exists. How can I make it so that it will replace it directly without asking?