Python: error opening excel workbook with win32com

Question

I am trying to make a program to open existing excel files, in order to read and write their contents. I am already having trouble opening files.

My code:

def open_sheet():
    excel = win32com.client.Dispatch("Excel.Application")
    wb = excel.Workbooks(r'C:\Users\<username>\Documents\<more file path>\test.xls').Open()
    ws = wb.Worksheets('abc') # there is a worksheet called abc.
    excel.Visible = True

Note: I abbreviated the file paths for brevity. The .py file and the test.xls file are in the same folder.

The error:

Traceback (most recent call last):
  File "C:\Users\<username>\Documents\<more file path>\automation code.py", line 37, in <module>
    open_sheet()
  File "C:\Users\<username>\Documents\<more file path>\automation code.py", line 33, in open_sheet
    wb = excel.Workbooks(r'C:\Users\<username>\Documents\<more file path>\test.xls').Open()
  File "C:\Users\<username>\AppData\Local\Programs\Python\Python36-32\lib\site-packages\win32com\gen_py\00020813-0000-0000-C000-000000000046x0x1x9\Workbooks.py", line 198, in __call__
    ret = self._oleobj_.InvokeTypes(0, LCID, 2, (13, 0), ((12, 1),),Index
pywintypes.com_error: (**-2147352567**, '例外が発生しました。', (0, None, None, None, 0, -2147352565), None)

Note: I am using a Japanese computer. The sentence in Japanese means 'Exception occurred'.

I tried to find out more about the error:

import win32api
win32api.FormatMessage(**-2147352567**)

Output:
'例外が発生しました。\r\n'
(The translation, I think, is: 'Exception occurred. \r\n')

I found this question. In it, the error number is the same, but their problem is the wrong sheet name. My sheet name, 'abc' really does exist in the excel file.

The following might also be useful diagnostic information. This code works (to create and open a new workbook):

def make_xl():
    o = win32com.client.Dispatch("Excel.Application")
    o.Visible = 1
    o.Workbooks.Add() # for office 97 – 95 a bit different!
    o.Cells(1,1).Value = "Hello"

Other info: Windows 10 Python 3.6 Python win is installed (pywin32 build 222)

I am new to using win32com. Please help me troubleshoot this problem.

Answer 1

I think your problem comes from:

wb = excel.Workbooks(r'C:\Users\<username>\Documents\<more file path>\test.xls').Open()

The path to your file should be in Open() such as:

wb = excel.Workbooks.Open(r'C:\Users\<username>\Documents\<more file path>\test.xls')

Note also that there is no () after Workbooks as in your second code. This way works for me.