how to add drop down list in excel cell using win32com python

Question

I am trying to add drop down in excel cell using python win32com api. But not able to implement it.

Here is my code

from win32com.client import Dispatch
import os
import win32api
path = os.getcwd()

path1 = path + '\\myExcel.xlsx'
try:
    xl = Dispatch("Excel.Application")   
    xl.Visible = 1  # fun to watch!
    wb = xl.Workbooks.Open(Filename=path1)

    ws = wb.Worksheets(1)

    ws.Cells(1,1).Value = "GREEN"
    ws.Cells(2,1).Value = "YELLOW"
    ws.Cells(3,1).Value = "RED"
    ws.Cells(4,1).Value = "WHITE"
    ws.Cells(5,1).Value = "NOT SURE"
    ws.Cells(6,1).Value = "["GREEN", "YELLOW", "RED", "WHITE", "NOT SURE"]" //I want drop down here
    wb.Close(SaveChanges=True)
    xl.Quit()
except Exception as e:
    print(e)

What OP wants to do is what you can do interactively in Excel 2016 this way: Data tab | Data Tools pane | Data Validation | Data Validation... | Settings | Allow: List, Source: <cell reference>. Here is a way to do it in VBA: software-solutions-online.com/excel-vba-drop-down-lists/#Jump2 — BoarGules
– BoarGules, Commented Jan 7, 2019 at 11:16
@SaurabhAgrawal instead of setting ws.Cells(6,1).Value the way your code is doing. set ws.Cells(6,1).Validation instead, following the VBA example. All Excel documentation of this sort assumes you are programming in VBA so you need to learn to transpose solutions into Python. — BoarGules
– BoarGules, Commented Jan 7, 2019 at 11:46

BoarGules · Accepted Answer · 2019-01-08 22:35:37Z

4

What you are doing isn't working because this line

ws.Cells(6,1).Value = "["GREEN", "YELLOW", "RED", "WHITE", "NOT SURE"]" //I want drop down here

is setting the value of the cell, just like the previous lines did. (Or rather, attempting to set it: that line contains two syntax errors, one in the quoting and one in the comment.)

But you don't want to set the value of the cell, you want to apply validation to the cell. So you need to set attributes of the object ws.Cells(6,1).Validation.

Taking just the code inside your try...except clause, that would look like this:

xl = Dispatch("Excel.Application")   
xl.Visible = 0  # Not really such fun to watch because the code below closes down Excel
                # straightaway. xl.Visible = 1 will just show a screen flicker.
wb = xl.Workbooks.Open(Filename=path1)

ws = wb.Worksheets(1)

ws.Cells(1,1).Value = "GREEN"
ws.Cells(2,1).Value = "YELLOW"
ws.Cells(3,1).Value = "RED"
ws.Cells(4,1).Value = "WHITE"
ws.Cells(5,1).Value = "NOT SURE"

# Set up validation
val = ws.Cells(6,1).Validation
val.Add(Type=3, AlertStyle=1, Operator=1, Formula1="=Sheet1!A1:A5")
val.IgnoreBlank = -1
val.InCellDropdown = -1
val.InputTitle = ""
val.ErrorTitle = ""
val.InputMessage = ""
val.ErrorMessage = ""
val.ShowInput = -1
val.ShowError = -1

wb.Close(SaveChanges=True)
xl.Quit()

The lines that set up the validation follow exactly the example in the reference given in my comment. The objects that win32com gets from Excel are not Python objects: they are thin Python wrappers around VBA objects, and these VBA objects follow their own conventions, not Python's. So the Python code follows the VBA exactly, except for syntax. The only differences are cosmetic.

.Add gets parentheses because functions in Python have to have them (VBA methods don't).
Named parameters to methods get a Python = not a VBA :=.
Constants like xlBetween represent integer values; you can find the values on MSDN.
VBA defines True as -1. 1 or True will probably also work: I didn't try.
Python doesn't have an equivalent of VBA's with statement so val has to be explicit in assignments like val.ErrorMessage = "" instead of implicit as in VBA.

This is the result I got.

edited Jan 8, 2019 at 22:35

answered Jan 8, 2019 at 13:26

BoarGules

17.1k3 gold badges29 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Saurabh Agrawal Over a year ago

Ahhh that's what I was missing in my code.. Thank you so much for your help BoarGules. But seems like val.IgnoreBlank = True isn't working because if cell in blank then it's still showing in drop down.

BoarGules Over a year ago

It only appears not to work. Ignore Blank does not mean that Excel will ignore blank cells in the named range you supply in Formula1 and construct a drop-down list without them, as you seem to expect. It means that Excel will not complain about a blank value in the cell when blank is not one of the options in the drop-down list. mrexcel.com/forum/excel-questions/…

Saurabh Agrawal Over a year ago

Thanks for showing me some key points. Btw win32com internally using COM object which I want to avoid. Is there any other way to access full excel functionality without COM object.

BoarGules Over a year ago

I take it you have discovered that you can't deploy this solution to machines that don't have Excel installed. The win32com module uses COM. That is its purpose, as its name suggests. The xlwings module also uses COM, but, as its name suggests, is Excel-specific. You could try openpyxl. It has at least some support for validation (openpyxl.readthedocs.io/en/stable/validation.html) but I can't say if its support extends to full Excel functionality. The only software that can do everything that Excel does is Excel itself, and COM is the only way to access that programmatically.

praveen kumar Over a year ago

How to add list of record in dropdown, instead of Formula1="=Sheet1!A1:A5".

|

Collectives™ on Stack Overflow

how to add drop down list in excel cell using win32com python

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related