4

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.

Community
  • 1
  • 1
Stephanie
  • 45
  • 1
  • 7
  • This might not be the cause of the problem, but should be changed anyway: \ is an escape character. Either use 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')` – mechanical_meat Nov 14 '14 at 17:45
  • Also 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`: http://timgolden.me.uk/python/win32_how_do_i/start-a-new-com-instance.html – mechanical_meat Nov 14 '14 at 17:59
  • Hi Bernie. Thank you for those recommendations. I made the adjustments you suggested. Unfortunately I am still getting the error. – Stephanie Nov 14 '14 at 18:33
  • The error being on that line suggests that there is no "Sheet1" in WorkbookB.xlsx – mechanical_meat Nov 14 '14 at 18:41
  • Yep...I had changed the name of Sheet1 to Charts. Thank you Bernie! – Stephanie Nov 14 '14 at 21:11

1 Answers1

6

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
mechanical_meat
  • 163,903
  • 24
  • 228
  • 223
  • Hello how are you I have a question regarding something similar to this question. In my case i need to copy the info from a sheet1 in the workbookA, to the sheet2 in the workbookB. I try to use the code you give to Stephanie in her question, but in my case is creating a new sheet in workbookB. You can find my question here. http://stackoverflow.com/questions/42234155/copying-from-xlsx-to-an-specific-sheet-in-another-xlsx I will really appreciate your help. Thanks and sorry for the bad english Regards, Carlos Arronte. – Carlos Arronte Bello Feb 22 '17 at 22:03
  • @CarlosArronteBello: please go ahead and ask another question with your requirements. Thank you. – mechanical_meat Feb 22 '17 at 22:13
  • Thanks i will do asap. I'm not in the office right now. Will be back something like 90 minutes. Thank you very much. – Carlos Arronte Bello Feb 22 '17 at 22:46
  • Here is my [Question](http://stackoverflow.com/questions/42404680/copying-information-from-one-workbook-to-another-workbook-using-win32com) – Carlos Arronte Bello Feb 23 '17 at 00:08
  • I just made a little update to the question. See link above – Carlos Arronte Bello Feb 23 '17 at 01:46