CODEWITHSUNDEEP
excelpython-3.xvbaapixlwings
Nils
Nils

Reputation: 11

How to call VBA WorksheetFunction.Match using xlwings worksheet.api?

I have an Excel table from which I need to look up a specific value in column A and want to get the row number. For example, column A in the Excel sheet contains numbers from 1 to 50 and cell B2=10.

I have tried to call WorksheetFunction.Match(arg1,arg2,arg3) (https://learn.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel.worksheetfunction.match?view=excel-pia#Microsoft_Office_Interop_Excel_WorksheetFunction_Match_System_Object_System_Object_System_Object_) using xlwings worksheet.api, but get and "AttributeError: .WorksheetFunction" when using it in Python (the WorksheetFunction.Match() works fine in VBA).

import xlwings as xw
wb=xw.Book(r'test.xlsm')
ws=wb.sheets['Test']
row_number=ws.api.WorksheetFunction.Match(Range("B2").Value, Range("A1:A50"), 0)

In this example I expect to get row_number=10, but instead get:

Traceback (most recent call last):
  File "C:\tools\anaconda3\5.3.0\envs\nilspy\lib\site-packages\xlwings\_xlwindows.py", line 117, in __getattr__
    v = getattr(self._inner, item)
  File "C:\tools\anaconda3\5.3.0\envs\nilspy\lib\site-packages\win32com\client\dynamic.py", line 527, in __getattr__
    raise AttributeError("%s.%s" % (self._username_, attr))
AttributeError: <unknown>.WorksheetFunction
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
  File "C:\tools\anaconda3\5.3.0\envs\nilspy\lib\site-packages\IPython\core\interactiveshell.py", line 2961, in run_code
    exec(code_obj, self.user_global_ns, self.user_ns)
  File "<ipython-input-24-2c337b4c3c07>", line 5, in <module>
    ws.api.WorksheetFunction.Match(Range("B2").Value, Range("A1:A50"), 0)
  File "C:\tools\anaconda3\5.3.0\envs\nilspy\lib\site-packages\xlwings\_xlwindows.py", line 137, in __getattr__
    self._oleobj_.GetIDsOfNames(0, item)
pywintypes.com_error: (-2147352570, 'Unknown name.', None, None)

I'm grateful for all help!

Edit: The last row in the code should probably refer to ws.range, like this: import xlwings as xw wb=xw.Book(r'test.xlsm') ws=wb.sheets['Test'] ws.api.WorksheetFunction.Match(ws.range('B2').value, ws.range('A1:A50'), 0) However, it results in the same error.

Upvotes: 1

Views: 939

Answers (1)

QHarr
QHarr

Reputation: 84465

You could use find ( I can't find any documentation on worksheet functions being called)

ws = wb.sheets['Test']
search_range = ws.range("A1:A" + str(ws.range("A" + str(ws.cells.rows.count)).end('up').row))
search_range.api.find(ws.range("B2").value).row

The value may not be present so you would be better setting result of find to a variable first and testing if None:

found = search_range.api.find(ws.range("B2").value)

r = found.row if found is not None else '-999'  #some value to indicate not found

Upvotes: 1

Related Questions