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)

Answer 1

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.

1. .Add gets parentheses because functions in Python have to have them (VBA methods don't).
2. Named parameters to methods get a Python = not a VBA :=.
3. Constants like xlBetween represent integer values; you can find the values on MSDN.
4. VBA defines True as -1. 1 or True will probably also work: I didn't try.
5. 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.