Excel Data List Validation in Powershell

Posted by idazuwaika on Stack Overflow See other posts from Stack Overflow or by idazuwaika
Published on 2010-03-26T06:57:33Z Indexed on 2010/03/26 7:03 UTC
Read the original article Hit count: 340

Filed under:

excel

|

powershell

|

data-validation

Hi,

I have a range named "STATE". I want to set data validation in range ("A1") to only take value within this range using Powershell.

Below is what I have tried. Does not work. I dont know what to put as 4th and 5th parameters. The first 3 are Excel constants equivalent to xlValidateList, xlValidAlertStop and xlBetween respectively.

$ws.Range("A1").Validation.Add(3, 1, 1, "=STATE", 0)

Please help. Thanks.

© Stack Overflow or respective owner

Related posts about excel

Excel error "This workbook contains Excel 4.0 macros or Excel 5.0 modules"

as seen on Super User - Search for 'Super User'
I have a workbook that was protected via the Protect Workbook feature. It was sent to someone else to modify. When they sent it back, it was unprotected and when I try to reprotect it I get this error, "This workbook contains Excel 4.0 macros or Excel 5.0 modules. If you would like to… >>> More
Open excel 2007 excel files and save as 97-2003 formats in VBA

as seen on Stack Overflow - Search for 'Stack Overflow'
I have a weird situation where I have a set of excel files, all having the extension .xls., in a directory where I can open all of them just fine in Excel 2007. The odd thing is that I cannot open them in Excel 2003, on the same machine, without opening the file first in 2007 and going and saving… >>> More
Creating Excel or Excel compatible Spreadsheets on the server side in C#

as seen on Stack Overflow - Search for 'Stack Overflow'
I'd like to make server-side excel compatible spreadsheets that maybe use OpenXML or a structured data format. I've used Office Interop before to generate Excel spreadsheets, but those apps run on a PC that has office installed. For this web project I'm building, the server doesn't have office installed… >>> More
vba access values from excel workbook from another excel workbook

as seen on Stack Overflow - Search for 'Stack Overflow'
Any ideas why this code will not work? Workbooks.Open Filename:="C:\a.xls" Workbooks("a.xls").Activate Worksheets("b").Select Dim OutputArray(10, 10) OutputArray(1, 1) = Workbooks("c.xls").Worksheets("worksheet_otherthan_default").Select.Range(A1).Value I'm trying to run a macro in one file (unnamed… >>> More
Excel workbook event order and usage when closing Excel

as seen on Stack Overflow - Search for 'Stack Overflow'
Given the following workbook events: BeforeClose BeforeSave Please tell me: - The firing order in the case of multiple workbooks alreay opened (wb1, wb2 and wb3 are opened in this order) and the user closes Excel. You can assume all 3 needs saving. - What happen if user cancels one of the saving… >>> More

Related posts about powershell

In Powershell on Windows7, how do I automatically set aliases (or run scripts) when powershell loads

as seen on Super User - Search for 'Super User'
I have created a powershell cmdlet and assigned it to an Alias. How do I do this automatically every time powershell launches? (i.e. so I don't have to re-assign the alias every time) >>> More
How to Load commands into your powershell profile to run on starting powershell

as seen on Server Fault - Search for 'Server Fault'
Hi, I have found a way to load exchange 2010 powershell into powershell running on a windows xp workstation, however there are a few commands that need to run. I was wondering how I could load them into a profile somehow. These are the commands that I need to run before I can do any of the exchange… >>> More
C#, Powershell - Microsoft.Exchange.Management.PowerShell.Admin

as seen on Stack Overflow - Search for 'Stack Overflow'
I'm having troubles using the Microsoft.Exchange.Management.PowerShell.Admin on a server. The server is not the one running Exchange 2007, it's a remote server (in the same zone). I can't figure out how to add the Snapin for Powershell - Microsoft.Exchange.Management.PowerShell.Admin. Is it possible… >>> More
PowerShell Script to Create PowerShell Profile

as seen on Geeks with Blogs - Search for 'Geeks with Blogs'
Utilizing a PowerShell profile can help any PowerShell user save time getting up and running with their work. For those unfamiliar a PowerShell profile is a file you can store any PowerShell commands that you want to run when you fire up a PowerShell console (or ISE.) In my typical profiles (example… >>> More
Download Free PowerShell Quick Reference Guides from Microsoft

as seen on How to geek - Search for 'How to geek'
Are you just getting started with learning PowerShell or tired of looking up less frequently used commands? Then this terrific set of PowerShell quick reference guides from Microsoft is just what you need! The first guide focuses on commonly-used Windows PowerShell commands and is available in a single… >>> More