copy entire excel worksheet to a new worksheet using Python win32com

Question

I try to open an existing workbook called 'N11 Result.xlsx'. Copy the entire worksheet 'Sheet1' (rename it to'Built with Python' first...) and paste to a new worksheet(named 'Annual') within the same workbook. I think the last line of my code doesn't work... any help will be helpful.

import os,sys
sys.path.append(r"""U:\Programming\Python\Python Manipulate Excel""")
work_dir=r"""U:\Programming\Python\Python Manipulate Excel"""
file_name='N11 Result.xlsx'

import win32com.client
excel = win32com.client.Dispatch('Excel.Application')
excel.Visible = True

wb = excel.Workbooks.Open(work_dir+'\\'+file_name)
ws = wb.Worksheets('Sheet1')
ws.Name = 'Built with Python'
wb.Worksheets("Built with Python").Copy(before=wb.Worksheets("Annual"))
excel.Application.Quit()

The error message showed up will be

Traceback (most recent call last): File "U:\Programming\Python\Python Manipulate Excel\Test_createExcel_01.py", line 17, in wb.Worksheets("Built with Python").Copy(before=wb.Worksheets("Annual")) File "C:\Temp\gen_py\2.7\00020813-0000-0000-C000-000000000046x0x1x7\Sheets.py", line 113, in call ret = self._oleobj_.InvokeTypes(0, LCID, 2, (9, 0), ((12, 1),),Index com_error: (-2147352567, 'Exception occurred.', (0, None, None, None, 0, -2147352565), None)

Maybe look at one of the Python libraries for doing just this: xlwt/xlrd openpyxl, xlsxwriter, etc. In my experience this is much simpler than messing with win32com — SiHa, May 13 '16 at 12:19
@SiHa You are definitely right. I start using openpyxl, do you know any book or website I should check to learn more about openpyxl? thanks! — BenSeedGangMu, Jul 10 '18 at 20:47
How about the official documention? https://openpyxl.readthedocs.io/en/stable/ It's very thorough. — SiHa, Jul 10 '18 at 21:14

score 3 · Answer 1 · edited Feb 21 '20 at 14:46

3

So this is how you can copy an entire worksheet into a new sheet using pywin32:

# old_sheet: sheet that you want to copy
old_sheet.Copy(pythoncom.Empty, workbook.Sheets(workbook.Sheets.Count))
new_sheet = workbook.Sheets(workbook.Sheets.Count)
new_sheet.Name = 'Annual'

edited Feb 21 '20 at 14:46

Simas Joneliunas

2,890
20
28
35

answered May 13 '16 at 12:02

t-rex42

31
2

After I change `pythoncom.Empty` into `None`, it worked. Thank you a lot. :) – Mystic Lin Apr 30 '17 at 16:26

score 3 · Answer 2 · answered Mar 09 '17 at 14:59

    import win32com.client as win32
    from copy import copy
    excel = win32.gencache.EnsureDispatch('Excel.Application')
    #excel.Visible = False
    excel.DisplayAlerts = False
    wb0 = excel.Workbooks.Open(dirname + '\\' + 'original.xlsx')
    ws0 = wb0.Worksheets('Original_sheet')
    wb2 = excel.Workbooks.Open(dirname + '\\' + writer.path)
    ws2 = wb2.Worksheets.Add()
    ws2.Name = 'Copy_original'
    ws2 = wb2.Worksheets('Copy_original')
    ws0.Range("A1:AF100").Copy(ws2.Range("A%s:AF%s" % (row, col)))
    wb2.Save()
    excel.Application.Quit()

score 0 · Answer 3 · answered Jun 09 '16 at 21:45

Try switching out wb.Worksheets("Built with Python").Copy(before=wb.Worksheets("Annual")) with the following code.

wb.Worksheets("Built with Python").Cells.Select()
excel.Selection.Copy(Destination=wb.Worksheets("Annual").Range("A1"))

Let me know if that works. If it doesn't work it may be the directory you're trying to build that might be the cause.

copy entire excel worksheet to a new worksheet using Python win32com

3 Answers3