Copy Excel sheet inside Excel file

[SAPER59]

I can't find the way to copy a sheet with another name inside Excel

I can create a new sheet but not with the cells format of existing one, and if possible how to move a shhet into another position in middle of other existing ones

Any sugestion will be thanks

Regards

[hak_vz · Accepted solution]

@SAPER59  Here is my test example that I guess you'll know how to use.

(defun c:excel_test( / excel)
(setq excel (vlax-create-object "Excel.Application"))
(setq workbooks (vlax-get excel 'workbooks))
(vlax-invoke-method workbooks 'Open "D:\\test_1.xlsx")
(setq activeWorkbook (vlax-get Excel 'ActiveWorkbook))
(setq sheets (vlax-get activeWorkbook 'WorkSheets))
(setq i 0)
(vlax-for sh sheets(setq i (1+ i) sheet_names (cons (cons (vlax-get sh 'Name) i) sheet_names)))
(setq sheet1_index (cdr (assoc "Sheet1" sheet_names)))
(setq sheet2_index (cdr (assoc "Sheet2" sheet_names)))
(setq sheet1 (vlax-get-property sheets 'Item sheet1_index))
(setq sheet2 (vlax-get-property sheets 'Item sheet2_index))
(vlax-invoke-method sheet1 'Copy sheet2)
(vlax-put excel 'visible 1)
)

This will create copy of Sheet1  i.e "Sheet1 (2)". After that you should rename new worksheet and use method move to rearrange sheets order inside workbook.

[SAPER59]

Thanks it works perfectly, just one more question how can I change the position of a sheet?

Thanks again I will accept it as a solution because it really is

[hak_vz]

In continuation to my previous code

(setq sheet_names nil i 0); reset list and counter
(vlax-for sh sheets(setq i (1+ i) sheet_names (cons (cons (vlax-get sh 'Name) i) sheet_names)))
(setq new_sheet_index (cdr (assoc "Sheet1 (2)" sheet_names)))
(setq new_sheet (vlax-get-property sheets 'Item new_sheet_index))
(vlax-put new_sheet 'Name "New Sheet")
(vlax-invoke-method new_sheet 'Move sheet1) ; puts new sheet in front sheet1
(vlax-invoke-method new_sheet 'Move sheet2) ; puts new sheet in front sheet2

Excel object method move has parameter "After" and "Before" but since I'm not so much versatile in scripting Excel maybe someone can step in. I hope that code above will help anyway.

[pbejse]

---

@hak_vz wrote:

(vlax-invoke-method sheet1 'Copy sheet2)
...
(vlax-invoke-method new_sheet 'Move sheet1)

Thats good to know.

I was wondering about why the OP would post that question in Lisp forum though,  would've gone straight to .Net for any Microsoft Office files manipulation but thats just me 🙂

[hak_vz]

 Microsoft Excel, Word and Powerpoint and  probably some other apps are easily accessible through visual lisp using its object models.

I have written this code by simply looking into Excel object model definition and I have never done this before. Someone with better knowledge of visual-lisp would probably come up with even better code. Another option is to use Autocad VBA (separate installation after version 2018) and run vba script from visual lisp.

OP is a long time application Autocad developer in visual-lisp and he uses Excel a lot, AFAIK from his previous requests. I'm sure he would come to solution on his own.

In worksheet methods it stands.

Worksheets("Sheet1").Copy After:=Worksheets("Sheet3")

How to rewrite this in visual lisp, and invoke "after" or "before" keyword to Copy method?

[pbejse]

---

@hak_vz wrote:

... OP is a long time application Autocad developer in visual-lisp and he uses Excel a lot, AFAIK from his previous requests. I'm sure he would come to solution on his own.

---

I have nothing against using vlisp to manipulate Microsoft products, only issue i have with it is, the program will only work with workstation that has Microsoft office installed. with VBA or NET, you can utilize the likes of OpenXML SDK It supports  word-processing documents, spreadsheets, and presentations outside of MSOffice.

Dont mind me, I was just thinking out loud.