copy entire excel worksheet to a new worksheet using Python win32com
Solution 1
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'
Solution 2
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()
BenSeedGangMu
Updated on June 04, 2022Comments
-
BenSeedGangMu almost 2 years
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)
-
Mystic Lin about 7 yearsAfter I change
pythoncom.Empty
intoNone
, it worked. Thank you a lot. :)