Hello All,
I wrote Python code to write data to an Excel worksheet1, and also add 5 status types to worksheet2 to be used to make a dropdown list for one column in worksheet1. Could someone help me with the code to do this in Python? I recorded the macro >> -
Sub Macro2()
-
'
-
' Macro2 Macro
-
'
-
-
'
-
Sheets("Sheet2").Select
-
Range("A1:A5").Select
-
Range("A5").Activate
-
ActiveWorkbook.Names.Add Name:="ValidStatus", RefersToR1C1:= _
-
"=Sheet2!R1C1:R5C1"
-
Sheets("Sheet1").Select
-
Columns("I:I").Select
-
With Selection.Validation
-
.Delete
-
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
-
xlBetween, Formula1:="=ValidStatus"
-
.IgnoreBlank = True
-
.InCellDropdown = True
-
.InputTitle = ""
-
.ErrorTitle = ""
-
.InputMessage = ""
-
.ErrorMessage = ""
-
.ShowInput = True
-
.ShowError = True
-
End With
-
End Sub
-
I have tried a few things (like below), but am stumped and can't find much documentation out there. I need to declare the name of the 5 cells on worksheet2 as 'ValidStatus' and get column I on worksheet1 to be a list with ValidStatus as the choices. -
ws1 = wb.Worksheets(1)
-
ws2 = wb.Worksheets(2)
-
-
ws2.Cells(1,1).Value = "GREEN"
-
ws2.Cells(2,1).Value = "YELLOW"
-
ws2.Cells(3,1).Value = "RED"
-
ws2.Cells(4,1).Value = "WHITE"
-
ws2.Cells(5,1).Value = "NOT SURE"
-
-
ws1.Columns("I").Validation.Add(Type = "xlValidateList", xlValidAlertStop, xlBetween, "=ValidStatus")
-
Any help would be greatly appreciated. And, if anyone could refer me to some good documentation on Python and Excel, well that would be great!
Thanks,
jenn
2 14502
Add-on -->
I finally (been looking/asking for months) ran across a method to get at the value of Excel constants for use in Pyhton scripting.
From http://aspn.activestate.com/ASPN/Coo.../Recipe/528870 ... To get the value of Excel Constants such as xlEdgeLeft (7) or xlThin (2)
type e.g. Debug.Print xlEdgeLeft in the Immediate window of the VBA editor and press enter. This helps for general knowledge, but still have not gotten my script to work...
I figured it out myself after many hours!!! Solution is: - ws1 = wb.Worksheets(1)
-
ws2 = wb.Worksheets(2)
-
-
ws2.Cells(1,1).Value = "GREEN"
-
ws2.Cells(2,1).Value = "YELLOW"
-
ws2.Cells(3,1).Value = "RED"
-
ws2.Cells(4,1).Value = "WHITE"
-
ws2.Cells(5,1).Value = "NOT SURE"
-
-
wb.Names.Add("ValidStatus", "=vl!R1C1:R5C1")
-
ws1.Columns("I").Validation.Add(3, 1, 1, "=ValidStatus") ##Type = xlValidateList/3, AlertStyle = xlvalidAlertStop/1, Operator=xlBetween/1, Formula1="=ValidStatus"
-
-
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Markus |
last post by:
hi,
is there a way to set up an user, how only have restricted admin
rights on mysql server. this user only should be able to create DBs
(and drop them) and set grants on this new DB.
i think...
|
by: Karen Sullivan |
last post by:
Hi, all. I'm fairly new to SQL, and I have been trying to create a table
from a text file. I have been looking at this for days, and can't find the
problem. I get a syntax error " Line 55:...
|
by: Manzoorul Hassan |
last post by:
I just Installed MySQL v4.1.14 and had created a Database (from a
script). But I would like to redo the Database portion of it but am not
able to drop the Database. If I do a "show database;" I...
|
by: tom horner |
last post by:
Any ideas on why drop and create alias statements would take a long
time?
We recently went through an upgrade of our production database, which
included alter statements to table structures,...
|
by: Peter |
last post by:
Hello£¬everyone,
My program will collect a testing machine's data ,save the data and deal
with the data everyday. I want to use vb.net to create database, add and
delete tables or modify the...
|
by: Darren |
last post by:
I'm trying to create a file using drag and drop.
I want to be able to select a listview item drag it to the shell and create a file.
Each icon in the listview represents a blob in a database.
When...
|
by: masri999 |
last post by:
I have a requirement in SQL 2005 in Development database
1. Schema dbo owns all objects (tables,views,SPs,UDFs etc) .
2. Only DBA's ( who are database owners ) can create, alter tables ....
|
by: Rahul B |
last post by:
Hi,
I have a user UCLDEV1 which is a part of staff and a
group(db2schemagrp1) to which i have not given any permissions.
The authorizations of that user are shown as
db2 =get authorizations...
|
by: semaj.remle 'at' gmail |
last post by:
For files saved in source control, is it better to use code to DROP/
CREATE a procedure like this:
------------------------------------------------------------------
IF OBJECT_ID ('procName') IS...
|
by: Eric Isaacs |
last post by:
Optional Create and Alter is nice because it fails, the original is
still in place as are the original permissions. Drop and create is
also valid in some cases if you want to clear/reset the...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |