Read Dropdown list from Excel column

[ldiowa]

Hi,

How to retrieve all items in a dropdown list in a Excel cell using C#? Furthermore, can the SelectedValue of the dropdown be set using C#? I am using Office 2007.

Thanks very much for your help.

Hugh

 

[GS]

Hi,

How to retrieve all items in a dropdown list in a Excel cell using C#?
Furthermore, can the SelectedValue of the dropdown be set using C#? I am
using Office 2007.

Thanks very much for your help.

Hugh

Try doing it manually in Excel while the macro recorder is running.
This will give you the correct refs to use in your code. Just make the
necessary language syntax adjustments and you're done!

--
Garry

Free uenet access at http://www.eternal-september.org
Classic VB Users Regroup
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[ldiowa]

Hi Garry,

Thanks for your help. I will take a look your link. For the second part, I used
oSheet.Range["A1"].Value = "Value", it does not work. Howevr, the same syntax works for regular cells (cells that don't have a dropdown associated with). Thank again.

Hugh

 

[GS]

Hi Garry,

Thanks for your help. I will take a look your link. For the second part, I
used oSheet.Range["A1"].Value = "Value", it does not work. Howevr, the same
syntax works for regular cells (cells that don't have a dropdown associated
with). Thank again.

Hugh

Note that the cell with a DV list (dropdown) can only hold values from
its DV list when being edited. That means if your text "Value" isn't in
the DV list then you can't insert it, and so it appears as though
nothing happened - assuming notification prompts for that cell are
turned off. Otherwise, you'll generate the 'invalid input' prompt.

--
Garry

Free uenet access at http://www.eternal-september.org
Classic VB Users Regroup
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[ldiowa]

Hi Gary,

I don't follow you. I already have a excel file that has a dropdown list in a cell. I want to retrieve all list items using C# and don't know how todo it. Yes, I can manually click the arrow on the cell and all items in the dropdown will be in display. However, I don't know if there is a "procedure" to get these values. Thanks.

Hugh

 

[isabelle]

hi ,Hugh,

with vba,

x = Range("A1").Validation.Formula1

isabelle

Le 2013-08-13 16:33, (e-mail address removed) a écrit :

Hi Gary,

I don't follow you. I already have a excel file that has a dropdown list in a cell.

I want to retrieve all list items using C# and don't know how to do
it. Yes,

I can manually click the arrow on the cell and all items in the
dropdown will be in display. However,

I don't know if there is a "procedure" to get these values. Thanks.

 

[GS]

Hi Gary,

I don't follow you. I already have a excel file that has a dropdown list in
a cell. I want to retrieve all list items using C# and don't know how to do
it. Yes, I can manually click the arrow on the cell and all items in the
dropdown will be in display. However, I don't know if there is a "procedure"
to get these values. Thanks.

Hugh

Ah, I see now what the issue is. Look in the Object Browser under
Range().Validation to see the properties/method available. Press F1 for
further info/examples to see what you need to grab from for your list.
For example, if the list is hard-entered items then...

oSheet.Range("A1").Validation>Formula1

...returns the list as entered. So if the list is '1,2,3,4,5' then it
might be auto-typed as a string "1,2,3,4,5" when put into a variable.

If it holds a ref to a range where the list is stored then it will
return the range address prepended with an equal symbol...

=$F$1:$J$1

...that you can parse however you want to get the list items however you
want.

If it holds a ref to a defined name where the list is stored then it
gets a bit more complex depending on whether the defined name refs a
range or just stores the list. Either must be queried via the
Application.Evaluate method to returns the value in its 'RefersTo'
property. This will be a range ref or the list items and so your code
will need to parse accordingly.

In summary, you need to evaluate whether Formula1 contains a range
address or defined name. If the latter you need to evaluate whether the
defined name is local (sheet level) or global (workbook level) in
scope. (God forbid it's defined both ways, but if so I believe the
local scope name has precedence over global!!) Then you need to
determine if the defined name refs a range or holds a delimited string.

Note that there is only 1 method for Range().Validation, which is
'Modify'!

--
Garry

Free uenet access at http://www.eternal-september.org
Classic VB Users Regroup
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

Oops.., typo!

Ah, I see now what the issue is. Look in the Object Browser under
Range().Validation to see the properties/method available. Press F1 for
further info/examples to see what you need to grab from for your list. For
example, if the list is hard-entered items then...
oSheet.Range("A1").Validation.Formula1

..returns the list as entered. So if the list is '1,2,3,4,5' then it might be
auto-typed as a string "1,2,3,4,5" when put into a variable.

If it holds a ref to a range where the list is stored then it will return the
range address prepended with an equal symbol...

=$F$1:$J$1

..that you can parse however you want to get the list items however you want.

If it holds a ref to a defined name where the list is stored then it gets a
bit more complex depending on whether the defined name refs a range or just
stores the list. Either must be queried via the Application.Evaluate method
to returns the value in its 'RefersTo' property. This will be a range ref or
the list items and so your code will need to parse accordingly.

In summary, you need to evaluate whether Formula1 contains a range address or
defined name. If the latter you need to evaluate whether the defined name is
local (sheet level) or global (workbook level) in scope. (God forbid it's
defined both ways, but if so I believe the local scope name has precedence
over global!!) Then you need to determine if the defined name refs a range or
holds a delimited string.

Note that there is only 1 method for Range().Validation, which is 'Modify'!

--
Garry

Free uenet access at http://www.eternal-september.org
Classic VB Users Regroup
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[ldiowa]

hi ,Hugh, with vba, x = Range("A1").Validation.Formula1 isabelle Le 2013-08-13 16:33, (e-mail address removed) a écrit : > Hi Gary, > > I don't follow you. I already have a excel file that has a dropdown list in a cell. I want to retrieve all list items using C# and don't know how to do it. Yes, I canmanually click the arrow on the cell and all items in the dropdown will bein display. However, I don't know if there is a "procedure" to get these values. Thanks. > > Hugh >

Hi Isbelle,

Thanks for your reply. I tried the method you suggested prior my post. It return a null.

 

[ldiowa]

Hi Gary,

Thanks very much for your help. I need to give it more time to digest. Asa quick note, I tried the method of "Formula1" prior my post. It returnednull. The method was suggested by Isbelle, aanother helper here. I found that the dropdown was referenced from another sheet in the same excel file. I can easily retrieve the values from there. Retrieving dropdown list is only technical chanllenge for me now. However, I still don't know how to programmatically set a value for the dropdown. I tried:
oSheet.Range["A1"].Value = "A1". it did not work and no error message. Thanks ver much for your help again.

Hugh

 

[ldiowa]

Thanks all, especially, Gary for your help. I tried all tips and did not work. However, your help did broaden my knownledge about Excel programing. As I said, Practically, I can retrieve info from the sheet where the dropdown listitems from. I need to know how to set selecteItem in dropdown and will post a new topic. Thanks again for your help.

 

[GS]

Thanks all, especially, Gary for your help. I tried all tips and did not

work. However, your help did broaden my knownledge about Excel programing.
As I said, Practically, I can retrieve info from the sheet where the dropdown
listitems from. I need to know how to set selecteItem in dropdown and will
post a new topic. Thanks again for your help.

You're welcome. Glad to be whatever help I can!

You can get the range ref from...

Dim sRngRef$
sRngRef = oSheet.Cells(1, 1).Validation.Formula1

...as both I and Isabelle explained. Once you have the range ref you can
get the list items from...

Dim iPos%, sWksName$, sRngAddr$, vDVList, n&
iPos = InStr(sRngRef, "!")
sWksName = Replace(Left$(sRngRef, iPos - 1), "'", "")
sRngAddr = Mid$(sRngRef, iPos + 1)
vDVList = wkbSource.Sheets(sWksName).Range(sRngAddr)

...which loads the list into a 1-based 2D array. You can access the
items in the normal way...

Dim sListItem$
For n = LBound(vDVList) To UBound(vDVList)
sListItem = vDVList(n, 1)
Next

Note that vDVList is data type 'Variant' so the size of the array is
automagic based on the size of the range being assigned to it.

--
Garry

Free uenet access at http://www.eternal-september.org
Classic VB Users Regroup
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[ldiowa]

I need to take sometime to learn your code. Are these "$", "%" and "&" legitment VB syntax? or, they were system added junks?

 

[GS]

I need to take sometime to learn your code. Are these "$", "%" and "&"
legitment VB syntax? or, they were system added junks?

Sorry for the brevity.., it's a habit I've become accustomed to! Yes,
those symbols are valid VB[A] syntax for data types...

String $
Integer %
Long &

Also, I forgot to declare the object ref to the workbook...

Dim wkbSource As appXL.Workbook

...where appXL is the usual var name I use in VB6 automation. In your
case you may want to declare 'As Object' or 'As Variant' if using late
binding (ie: CreateObject)!

--
Garry

Free uenet access at http://www.eternal-september.org
Classic VB Users Regroup
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[ldiowa]

Thanks for your explanation. I tried your code again. and could not get through the first one.

object sRangeRef = oSheet.Cells[1,1].Validation.Formula1;

the error was "COMException was unhandled" Exception from HRESULT:"

 

[Claus Busch]

Hi Garry,

Am Wed, 14 Aug 2013 11:02:23 -0400 schrieb GS:

Dim sRngRef$
sRngRef = oSheet.Cells(1, 1).Validation.Formula1

..as both I and Isabelle explained. Once you have the range ref you can
get the list items from...

Dim iPos%, sWksName$, sRngAddr$, vDVList, n&
iPos = InStr(sRngRef, "!")
sWksName = Replace(Left$(sRngRef, iPos - 1), "'", "")
sRngAddr = Mid$(sRngRef, iPos + 1)
vDVList = wkbSource.Sheets(sWksName).Range(sRngAddr)

if the list is not in the same sheet you have to name the list. I don't
know what seperator data validation is on your system. In german system
I have a semicolon.
And I put the items in an array with:

myStr = Range("A1").Validation.Formula1
myArr = Split(myStr, ";")


Regards
Claus B.

 

[ldiowa]

Hi Garry, Am Wed, 14 Aug 2013 11:02:23 -0400 schrieb GS: > Dim sRngRef$ >sRngRef = oSheet.Cells(1, 1).Validation.Formula1 > > ..as both I and Isabelle explained. Once you have the range ref you can > get the list items from... > > Dim iPos%, sWksName$, sRngAddr$, vDVList, n& > iPos = InStr(sRngRef, "!") > sWksName = Replace(Left$(sRngRef, iPos - 1), "'", "") > sRngAddr = Mid$(sRngRef, iPos + 1) > vDVList = wkbSource.Sheets(sWksName).Range(sRngAddr) if the list is not in the same sheet you have to name the list. I don't know what seperator data validation is on your system. In german system I have a semicolon. And I put the items in an array with: myStr = Range("A1").Validation.Formula1 myArr = Split(myStr, ";") Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2

Hi Claus,

Thanks for your help.

I tried
myString = oSheet.Range("A1").Validation.Formula1 many times and always received the error of HRESULT:0x800A0EC. It is a generic Excel error and means that Formula1 not found. Yes, the lititems are from another sheet.

 

[Claus Busch]

Hi,

Am Wed, 14 Aug 2013 09:20:32 -0700 (PDT) schrieb (e-mail address removed):

myString = oSheet.Range("A1").Validation.Formula1 many times and always received the error of HRESULT:0x800A0EC. It is a generic Excel error and means that Formula1 not found. Yes, the lititems are from another sheet.

try:
myStr = Sheets("Sheet1").Range("A1").Validation.Formula1
myArr = Split(myStr, ";")

Regards
Claus B.

 

[isabelle]

hi Hugh,

do you tried it on excel vba or on vb6 ?

isabelle

Le 2013-08-14 12:20, (e-mail address removed) a écrit :

I tried
myString = oSheet.Range("A1").Validation.Formula1

times and always received the error of HRESULT:0x800A0EC.

It is a generic Excel error and means that Formula1 not found. Yes, the
lititems are from another sheet.

 

[GS]

Hi Garry,

Am Wed, 14 Aug 2013 11:02:23 -0400 schrieb GS:


if the list is not in the same sheet you have to name the list. I don't
know what seperator data validation is on your system. In german system
I have a semicolon.
And I put the items in an array with:

myStr = Range("A1").Validation.Formula1
myArr = Split(myStr, ";")


Regards
Claus B.

Yep, that'll work so long as the list is hard-entered in the DV refedit
box, AND a semicolon was used as a delimiter. (My sys is US English and
uses whatever delimiter we specify in the refedit box) Not sure what
Excel uses in case of a range ref but your example code would result in
a 2D array if passing the range address instead of reading hard-entered
Formula1 content. Herein lies the complexity! Does Formula1 contain a
delimited string or a range ref? If former, what delimiter is used? If
latter, is the range ref a defined name or an address? If former then
its content must be evaluated OR the range must be enumerated for its
items. Adding to complexity is whether the range ref includes its
sheetname, and what its scope is.

--
Garry

Free uenet access at http://www.eternal-september.org
Classic VB Users Regroup
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion