4

I am trying to add excel worksheets to the end of a workbook, reserving the first sheet for a summary.

import win32com.client

Excel = win32com.client.DispatchEx('Excel.Application')
Book = Excel.Workbooks.Add()

Excel.Visible = True

Book.Worksheets(3).Delete()
Book.Worksheets(2).Delete()

Sheet = Book.Worksheets(1)

Sheet.Name = "Summary"

Book.Worksheets.Add(After=Sheet)

Sheet = Book.Worksheets(2)

Sheet.Name = "Data1"

This code adds the new sheet to the left, despite using After=Sheet, and when I modify the sheet named "Data1", it overwrites the sheet named "Summary".

This is similar to this problem:

Adding sheets to end of workbook in Excel (normal method not working?)

but the given solutions don't work for me.

Community
  • 1
  • 1
PProteus
  • 549
  • 1
  • 10
  • 23
  • Instead of `After = Sheet`, try `After = Sheets.Count` (I'm not too keen on PythonVBA, but you want to make sure you add it after the last sheet. – BruceWayne Oct 21 '16 at 14:59
  • What happens when you try the given solutions? – wwl Oct 21 '16 at 15:01
  • Neither `After=Book.Worksheets.Count` nor `After=Book.Sheets(Book.Sheets.Count)` work. They give the same behavior described above. – PProteus Oct 21 '16 at 15:05
  • Another interesting anomaly is that when I use `Sheet.Move(After = Sheets.Count)` or similar, it moves Sheet to a new workbook. – PProteus Oct 24 '16 at 16:32
  • In VBA I use `Sheets.Add, Sheets(Sheets.Count)` as my shortcut to add a worksheet at the end... so *'that'* in Python. (I think you just need to add three `book` objects in this case.) – ashleedawg Dec 29 '18 at 11:13

3 Answers3

16

Try using this by adding Before = None:

add = Book.Sheets.Add(Before = None , After = Book.Sheets(book.Sheets.count))
add.Name = "Data1"
Stephen Rauch
  • 47,830
  • 31
  • 106
  • 135
Cheng
  • 196
  • 1
  • 10
1

Try using Sheet = excelApp.ActiveSheet:

Book.Worksheets.Add(After=Sheet)
Sheet = Book.ActiveSheet
Sheet.Name = "Data1"
Oliver
  • 27,510
  • 9
  • 72
  • 103
-1

import win32com.client as win32

xl = win32.gencache.EnsureDispatch('Excel.Application') xl.Sheets.Add(After=xl.ActiveSheet).Name ="Name_of_your_Sheet"

BobOpen
  • 11