CODEWITHSUNDEEP
excelpython-3.xvbapywin32
Kevin
Kevin

Reputation: 3

pywin32 selecting all data in an excel column

I am very new to python, but used excel VBA before. I'm trying to use pywin32 module, as it is using COM to open and read/write excel along with many other reasons..

I have no problem writing formulas in pywin32 to write in excel i.e SUM, divide, minus etc. But I'm having trouble writing functions such as xlUp, CountIF etc in python.

In below code, I am trying to write a code that checks duplicates on all the cells in a column (i.e column "C")

Can you please help on below code?:

from win32com.client import Dispatch
from pathlib import Path
from datetime import datetime
import win32com.client.dynamic
data_folder = Path("C:/..")
file_open = data_folder / "something.xlsx"

xl = Dispatch("Excel.Application")

wb1 = xl.Workbooks.Open(Filename=file_open)
ws1 = wb1.Worksheets(1)

Target = ws1.Range("C3", ws1.Range("C", ws1.Rows.Count).End(xlUp)).Select

for r In Target:
   r.Offset(0, 1) = WorksheetFunction.CountIf(Target, r.Value) > 1

excel.Application.quit()

I don't know why it is not working.. how can I translate VBA to Python?

Thank you

Upvotes: 0

Views: 2004

Answers (1)

Mats Bengtsson
Mats Bengtsson

Reputation: 167

To generalize the answer on the last question:

pywin32 code is often very close to a one to one translation of VBA code. But there is a number of small changes needed. Like:

  • Many constants, are not defined by pywin32. The constant xlUp is defined. But it is reached as win32.constants.xlUp. So it explains one issue you have
  • Other constants are not defined. There you need to look up the value in Microsoft API documentation. For example "LookAt=xlWhole" becomes LookAt=1
  • Translation of VBA to python differs in the use of parenthesis when calling functions. Meaning that some attributes in VBA require "()" in the end to become valid code. Select is one example. It need to be used as ".Select()" instead of ".Select" to work. This explains another issue you are having.
  • A major exception to one to one translation is "Offset". By some strange reason, Offset(x,y) does not work. Wherever you in VBA use "Offset(x,y), you have to use "GetOffset(x,y). This explains a third reason your code does not work.
  • Finally variables in python are expected to start lower case. It is safe to do that, and to avoid variable names that might have a meaning. "Target" is safer expressed as "mytarget" or something unlikely to collide with built in functions.

I did not test the code, but the points I pointed out above does prevent your code from running. With mentioned changes I believe it would work. Most VBA code works as expected in pywin32 just adhering to above mentioned rules.

Upvotes: 2

Related Questions