CODEWITHSUNDEEP
pythonpython-2.7excelwin32comvba
Stephanie
Stephanie

Reputation: 45

Python 2.7 - win32com.client - Move a worksheet from one workbook to another

I'm trying to move one excel worksheet from workbook A to workbook B with python 2.7, but I keep getting an error.

Python script: 

import win32com.client

excel=win32com.client.Dispatch('Excel.Application')
excel.Visible=False
wbP=excel.Workbooks.Open('C:\Full Path\WorkbookA.xlsx')
wbG=excel.Workbooks.Open('C:\Full Path\WorkbookB.xlsx')
wbG.Worksheets("Sheet1").Select
wbG.Worksheets("Sheet1").Move(before=wbP.Worksheets("Annual"))
wbP.SaveAs('C:\Full Path\WorkbookA.xlsx')
excel.Application.Quit()

Error I'm receiving:

Traceback (most recent call last):
  File "C:\Full Path\test.py", line 10, in <module>
    wbG.Worksheets("Sheet1").Select
  File "C:\Python27\lib\site-packages\win32com\gen_py\00020813-0000-0000-C000-000000000046x0x1x8\Sheets.py", line 120, 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)

Thank you!

Solution:

See comments from bernie. The worksheet I needed moved was named Charts not Sheet1.

Upvotes: 4

Views: 7969

Answers (1)

mechanical_meat
mechanical_meat

Reputation: 169334

I'm writing the comments up in an answer because it's easier to read...

Since the error occurs on that line it appears that the problem is that there is no "Sheet1" in WorkbookB.xlsx

Below are some things you might want to change in the code:

  1. You can use win32com.client.DispatchEx to create a new instance of Excel to avoid interfering with any open Excel instances. If you use DispatchEx you can drop setting .Visible to False. Further reading about DispatchEx here: http://timgolden.me.uk/python/win32_how_do_i/start-a-new-com-instance.html

  2. \ is an escape character. Use either raw strings or forward-slashes, e.g.: wbG=excel.Workbooks.Open(r'C:\Full Path\WorkbookB.xlsx') or wbG=excel.Workbooks.Open('C:/Full Path/WorkbookB.xlsx')

Incorporating those suggestions the code becomes:

from win32com.client import DispatchEx

excel = DispatchEx('Excel.Application')
wbP=excel.Workbooks.Open(r'C:\Full Path\WorkbookA.xlsx')
wbG=excel.Workbooks.Open(r'C:\Full Path\WorkbookB.xlsx')
# note altered sheet name; also .Select is not required
wbG.Worksheets("Charts").Move(Before=wbP.Worksheets("Annual"))
wbP.SaveAs(r'C:\Full Path\WorkbookA.xlsx')
excel.Quit()
del excel # ensure Excel process ends

Upvotes: 7

Related Questions