Using win32.com to copy excel worksheet throws Microsoft Excel copy paste error

Question

When I use win32.com to open an excel file and paste the sheet into another excel file i get a copy paste error.

import win32com.client
import os

excel = win32com.client.Dispatch("Excel.Application")

w = excel.Workbooks.Open(os.path.join(os.getcwd(), "my_excel_file.xlsx"))

w.Sheets.Copy(wb.Sheets(1))

wb.SaveAs(os.path.join(os.getcwd(), "new_excel_file.xlsx"))
excel.Application.Quit()

This is the error I get:

pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft Excel', 'Excel cannot insert the sheets into the destination workbook, because it contains fewer rows and columns than the source workbook. To move or copy the data to the destination workbook, you can select the data, and then use the Copy and Paste commands to insert it into the sheets of another workbook.', 'xlmain11.chm', 0, -2146827284), None)

I see the same error as well. Do you have any updates on the problem? — LeanMan, May 27 '20 at 22:09
Hi I haven't been able to resolve this problem but a variety of other questions try to talk about this problem to no avail. This is the most similar one I found: https://stackoverflow.com/questions/40995672/python-win32com-invalid-number-of-parameters Hope it helps. — LeanMan, May 28 '20 at 01:55

LeanMan · Answer 1 · 2020-05-28T03:58:35.413

I was able to reproduce your error but it was not from the code you submitted. I changed your original code to reproduce the error. Additionally, there were missing information such as the "wb" variable was undefined but your issue is an Excel issue and not related to python or the Windows COM library. The following snippet of your error describes what your excel error is and the resources below that explains how you can get it.

'Excel cannot insert the sheets into the destination workbook, because it 
contains fewer rows and columns than the source workbook. To move or copy 
the data to the destination workbook, you can select the data, and then use 
the Copy and Paste commands to insert it into the sheets of another workbook'

In summary, its an issue if you take Microsoft Excel 2003 version and try to save it into a 2007 version.

Simply setting "wb.Sheets.Copy(w.Sheets(1))" to "w.Sheets.Copy(wb.Sheets(1))" will solve your problem. See code below:

import win32com.client
import os

try:
    excel = win32com.client.Dispatch("Excel.Application")
    wb = excel.Workbooks.Add()
    w = excel.Workbooks.Open(os.path.join(os.getcwd(), "my_excel_file.xlsx"))
    w.Sheets.Copy(wb.Sheets(1))
    wb.SaveAs(os.path.join(os.getcwd(), "new_excel_file.xlsx"))
finally:
    # Release resources
    wb = None
    w = None
    excel.Application.Quit()

Using win32.com to copy excel worksheet throws Microsoft Excel copy paste error

1 Answers1