Python 2.7 - win32com.client - move sheet from one workbook to another
I am trying to move one excel worksheet from Book A to Book B using python 2.7, but I keep getting the 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()
The error I am getting:
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!
Decision:
See comments from bernie. The worksheet I needed to be renamed was called Charts not Sheet1.
source to share
I write comments in response because it is easier to read ...
Since the error occurs on this line, it seems that the problem is that in WorkbookB.xlsx
no "Sheet1",Here are some things you can change in your code:
-
You can use
win32com.client.DispatchEx
to create a new instance of Excel to avoid interfering with any open instances of Excel. If you are usingDispatchEx
, you can reset the setting.Visible
toFalse
. Further readingDispatchEx
here: http://timgolden.me.uk/python/win32_how_do_i/start-a-new-com-instance.html -
\ is an escape character. Use either raw lines or fast forward like:
wbG=excel.Workbooks.Open(r'C:\Full Path\WorkbookB.xlsx')
orwbG=excel.Workbooks.Open('C:/Full Path/WorkbookB.xlsx')
By including these 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
source to share