Error trying to run functions.

[jembishop]

OS (e.g. Windows 10 or macOS Sierra)

Windows 10

Versions of xlwings, Excel and Python (e.g. 0.11.8, Office 365, Python 3.7)

Python 3.6.5
xlwings==0.15.0

Excel 2016 MSO (16.0.4639.1000)

Describe your issue (incl. Traceback!)

When I try to use the @xlwings.sub decorator, I get this error message when I try to run the macro in Excel. The @xlwings.func seems to work fine.

# Your traceback here
pythoncom error: Python error invoking COM method.

Traceback (most recent call last):
  File "E:\CODE\.virtualenvs\shifts\lib\site-packages\win32com\server\policy.py", line 278, in _Invoke_
    return self._invoke_(dispid, lcid, wFlags, args)
  File "E:\CODE\.virtualenvs\shifts\lib\site-packages\win32com\server\policy.py", line 283, in _invoke_
    return S_OK, -1, self._invokeex_(dispid, lcid, wFlags, args, None, None)
  File "E:\CODE\.virtualenvs\shifts\lib\site-packages\win32com\server\policy.py", line 586, in _invokeex_
    return func(*args)
  File "E:\CODE\.virtualenvs\shifts\lib\site-packages\xlwings\server.py", line 198, in CallUDF
    res = call_udf(script, fname, args, this_workbook, FromVariant(caller))
  File "E:\CODE\.virtualenvs\shifts\lib\site-packages\xlwings\udfs.py", line 246, in call_udf
    xw_caller.sheet.book.name + xw_caller.sheet.name + xw_caller.address.split(':')[0])
  File "E:\CODE\.virtualenvs\shifts\lib\site-packages\xlwings\main.py", line 1086, in sheet
    return Sheet(impl=self.impl.sheet)
  File "E:\CODE\.virtualenvs\shifts\lib\site-packages\xlwings\_xlwindows.py", line 697, in sheet
    return Sheet(xl=self.coords[0])
  File "E:\CODE\.virtualenvs\shifts\lib\site-packages\xlwings\_xlwindows.py", line 683, in coords
    self.xl.Worksheet,
AttributeError: 'int' object has no attribute 'Worksheet'

Include a minimal code sample to reproduce the issue (and attach a sample workbook if required!)

# Your code here
@xw.sub
def get_workbook_name():
    """Writes the name of the Workbook into Range("D3") of Sheet 1"""
    wb = xw.Book.caller()
    wb.sheets['Sheet1'].range('D3').value = wb.name

[fzumstein]

hm I can't replicate this. Does the very same function imported as func really work?

[jembishop]

No the same function imported as func does not work, I get this error message when I run:

@xw.func
def get_workbook_name():
    """Writes the name of the Workbook into Range("D3") of Sheet 1"""
    wb = xw.Book.caller()
    wb.sheets['Sheet1'].range('D3').value = wb.name

Traceback (most recent call last):
  File "E:\CODE\.virtualenvs\shifts\lib\site-packages\win32com\server\policy.py", line 278, in _Invoke_
    return self._invoke_(dispid, lcid, wFlags, args)
  File "E:\CODE\.virtualenvs\shifts\lib\site-packages\win32com\server\policy.py", line 283, in _invoke_
    return S_OK, -1, self._invokeex_(dispid, lcid, wFlags, args, None, None)
  File "E:\CODE\.virtualenvs\shifts\lib\site-packages\win32com\server\policy.py", line 586, in _invokeex_
    return func(*args)
  File "E:\CODE\.virtualenvs\shifts\lib\site-packages\xlwings\server.py", line 198, in CallUDF
    res = call_udf(script, fname, args, this_workbook, FromVariant(caller))
  File "E:\CODE\.virtualenvs\shifts\lib\site-packages\xlwings\udfs.py", line 302, in call_udf
    ret = func(*args)
  File "e:\code\git\airain\shifts\fill_in.py", line 68, in get_workbook_name
    wb.sheets['Sheet1'].range('D3').value = 3
  File "E:\CODE\.virtualenvs\shifts\lib\site-packages\xlwings\main.py", line 1517, in value
    conversion.write(data, self, self._options)
  File "E:\CODE\.virtualenvs\shifts\lib\site-packages\xlwings\conversion\__init__.py", line 43, in write
    pipeline(ctx)
  File "E:\CODE\.virtualenvs\shifts\lib\site-packages\xlwings\conversion\framework.py", line 66, in __call__
    stage(*args, **kwargs)
  File "E:\CODE\.virtualenvs\shifts\lib\site-packages\xlwings\conversion\standard.py", line 62, in __call__
    self._write_value(ctx.range, ctx.value, scalar)
  File "E:\CODE\.virtualenvs\shifts\lib\site-packages\xlwings\conversion\standard.py", line 50, in _write_value
    rng.raw_value = value
  File "E:\CODE\.virtualenvs\shifts\lib\site-packages\xlwings\main.py", line 1138, in raw_value
    self.impl.raw_value = data
  File "E:\CODE\.virtualenvs\shifts\lib\site-packages\xlwings\_xlwindows.py", line 724, in raw_value
    self.xl.Value = data
  File "E:\CODE\.virtualenvs\shifts\lib\site-packages\win32com\client\dynamic.py", line 565, in __setattr__
    self._oleobj_.Invoke(entry.dispid, 0, invoke_type, 0, value)
pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, None, None, None, 0, -2146827284), None)

However if I try and run for example:

@xw.func
def get_workbook_name():
    return 42

It works as expected.

Running this macro however:

@xw.sub
def get_workbook_name():
    print("Hello World")

Fails with the same error as the other @xw.sub function :

pythoncom error: Python error invoking COM method.

Traceback (most recent call last):
  File "E:\CODE\.virtualenvs\shifts\lib\site-packages\win32com\server\policy.py", line 278, in _Invoke_
    return self._invoke_(dispid, lcid, wFlags, args)
  File "E:\CODE\.virtualenvs\shifts\lib\site-packages\win32com\server\policy.py", line 283, in _invoke_
    return S_OK, -1, self._invokeex_(dispid, lcid, wFlags, args, None, None)
  File "E:\CODE\.virtualenvs\shifts\lib\site-packages\win32com\server\policy.py", line 586, in _invokeex_
    return func(*args)
  File "E:\CODE\.virtualenvs\shifts\lib\site-packages\xlwings\server.py", line 198, in CallUDF
    res = call_udf(script, fname, args, this_workbook, FromVariant(caller))
  File "E:\CODE\.virtualenvs\shifts\lib\site-packages\xlwings\udfs.py", line 246, in call_udf
    xw_caller.sheet.book.name + xw_caller.sheet.name + xw_caller.address.split(':')[0])
  File "E:\CODE\.virtualenvs\shifts\lib\site-packages\xlwings\main.py", line 1086, in sheet
    return Sheet(impl=self.impl.sheet)
  File "E:\CODE\.virtualenvs\shifts\lib\site-packages\xlwings\_xlwindows.py", line 697, in sheet
    return Sheet(xl=self.coords[0])
  File "E:\CODE\.virtualenvs\shifts\lib\site-packages\xlwings\_xlwindows.py", line 683, in coords
    self.xl.Worksheet,
AttributeError: 'int' object has no attribute 'Worksheet'

[fzumstein]

oh I see, sorry I was on the wrong version. I can replicate it now. It was introduced with 0.15.0, so if you downgrade to 0.14.1 for the moment it'll work until this is fixed.

[fzumstein]

fixed and released with 0.15.1

[rahullak]

I have the similar issue.

xlwings version 0.17.0
Python version 3.6.1

Please find attached xlsm and py files. Kindly help me resolve my issue. @fzumstein

trial.zip

[fzumstein]

@rahullak, in the future, please don't add stuff to closed issues but open a new one. You can't write to ranges from a function, you can only return a value.