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

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

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()

Suggestion : 2

I am trying to add drop down in excel cell using python win32com api. But not able to implement it.,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.),Taking just the code inside your try...except clause, that would look like this:,→ Shopify + Python library: how to create new shipping address

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 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

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()

Suggestion : 3

September 29, 2009 (Updated September 20, 2019) ,Originally posted on September 29, 2009 / Updated September 20, 2019

#
# driving.py
#
import win32com.client as win32
excel = win32.gencache.EnsureDispatch('Excel.Application')
excel.Visible = True
wb = excel.Workbooks.Add()
ws = wb.Worksheets('Sheet1')
ws.Name = 'Built with Python'
ws.Cells(1, 1).Value = 'Hello Excel'
print(ws.Cells(1, 1).Value)
for i in range(1, 5):
   ws.Cells(2, i).Value = i # Don 't do this
ws.Range(ws.Cells(3, 1), ws.Cells(3, 4)).Value = [5, 6, 7, 8]
ws.Range("A4:D4").Value = [i
   for i in range(9, 13)
]
ws.Cells(5, 4).Formula = '=SUM(A2:D4)'
ws.Cells(5, 4).Font.Size = 16
ws.Cells(5, 4).Font.Bold = True

Suggestion : 4

Let’s add a counter to our code so we can step through the cells and rows to find what we want to pull out. Be careful where you place the counter—you will have very different results if you place it on the cell level versus the row level.,If you run your script with the counter in it where count < 10, you will see from the output that we have not yet reached the row where the country names start.,If you run your complete code with the nested for loop, you will notice your output is not so helpful anymore. That brings us to the second mechanism to explore our Excel file—a counter.,If you scroll through your output, you will notice the majority of it looks good. But there are a couple of records that seem out of place.

First, we will be evaluating Excel data. Let’s install the package to do that— xlrd. To install the package, we use pip in the following way:

pip install xlrd

To remove the package, we would run the uninstall command:

pip uninstall xlrd

If you get the following error, that means you don’t have pip installed:

-bash: pip: command not found

To see how it works, open up your Python interpreter and try the following:

range(3)

The output should be:

[0, 1, 2]

A counter is a way to control the flow of your program. By using a counter, you can control your for loop by adding an if statement and increasing the count with each iteration of the loop. If the count ends up greater than a value of your choosing, the for loop will no longer process the code controlled by it. Try the following example in your interpreter:

count = 0

for i in range(1000):
   if count < 10:
   print i
count += 1

print 'Count: ', count

Create a sample list:

x = ['cat', 'dog', 'fish', 'monkey', 'snake']

To pull out the second item, you can refer to the item by adding an index, as shown here:

>>> x[2]
'fish'

If this isn’t the result you expected, remember that Python starts counting at 0. So, to get the second item as humans would identify it, we have to use the number 1:

>>> x[1]
'dog'

Slicing is another useful practice related to indexing. Slicing allows you to take a “slice” out of another list or iterable object. For example:

>>> x[1: 4]
   ['dog', 'fish', 'monkey']

If you don’t include the first or last number, the slice will go to the end. Here are a few examples:

x[2: ]
   ['fish', 'monkey', 'snake']

x[-2: ]
   ['monkey', 'snake']

x[: 2]
   ['cat', 'dog']

x[: -2]
   ['cat', 'dog', 'fish']

Use comments in your code as a way to help the future you (and others) understand why you did something. To comment in your code, put a # before the comment:

# This is a comment in Python.Python will ignore this line.

For a multiline comment, use the following format:

""
"
This is the formatting
for a multiline comment.
If your comment is really long or you want to
insert a longer description, you should use
this type of comment.
""
"