Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 24
Default Read Dropdown list from Excel column

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
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Read Dropdown list from Excel column

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


  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 24
Default Read Dropdown list from Excel column

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
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Read Dropdown list from Excel column

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


  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 24
Default Read Dropdown list from Excel column

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


  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Read Dropdown list from Excel column

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").ValidationFormula1

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


  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Read Dropdown list from Excel column

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


  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 24
Default Read Dropdown list from Excel column

On Tuesday, August 13, 2013 4:29:41 PM UTC-5, GS wrote:
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").ValidationFormula1 ..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


Hi Gary,

Thanks very much for your help. I need to give it more time to digest. As a quick note, I tried the method of "Formula1" prior my post. It returned null. 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


  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 24
Default Read Dropdown list from Excel column

On Tuesday, August 13, 2013 4:29:41 PM UTC-5, GS wrote:
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").ValidationFormula1 ..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


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.
  #12   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Read Dropdown list from Excel column

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


  #13   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 24
Default Read Dropdown list from Excel column

On Wednesday, August 14, 2013 10:02:23 AM UTC-5, GS wrote:
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


I need to take sometime to learn your code. Are these "$", "%" and "&" legitment VB syntax? or, they were system added junks?
  #14   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Read Dropdown list from Excel column

On Wednesday, August 14, 2013 10:02:23 AM UTC-5, GS wrote:
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


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


  #15   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 24
Default Read Dropdown list from Excel column

On Wednesday, August 14, 2013 10:56:41 AM UTC-5, GS wrote:
On Wednesday, August 14, 2013 10:02:23 AM UTC-5, GS wrote: 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 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


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



  #16   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Read Dropdown list from Excel column

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
  #17   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 24
Default Read Dropdown list from Excel column

On Wednesday, August 14, 2013 11:03:56 AM UTC-5, Claus Busch wrote:
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.
  #19   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 587
Default Read Dropdown list from Excel column

hi Hugh,

do you tried it on excel vba or on vb6 ?

isabelle

Le 2013-08-14 12:20, 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.

  #20   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Read Dropdown list from Excel column

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.


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




  #22   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Read Dropdown list from Excel column

Hi Garry,

Am Wed, 14 Aug 2013 12:41:25 -0400 schrieb GS:


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.


I tried it with both - values in validation and range ref. Both gives
back the values with semicolon as separator.
One time in validation I entered:
Test1;Test2;Test3
and another time I used
=myList
Semicolon is in german system the row separator. myList is vertical.


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
  #23   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Read Dropdown list from Excel column

Hi Garry,

Am Wed, 14 Aug 2013 18:46:14 +0200 schrieb Claus Busch:

Test1;Test2;Test3
and another time I used
=myList


my bad. I tested the hard coded validation two times
With a named range (=myList) it works:
Sub Test3()
Dim myArr As Variant
Dim myStr As String
Dim rngC As Range
Dim i As Integer

myStr = Replace(Range("A1").Validation.Formula1, "=", "")
ReDim myArr(Range(myStr).Cells.Count - 1)
For Each rngC In Range(myStr)
myArr(i) = rngC
i = i + 1
Next
End Sub


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
  #24   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Read Dropdown list from Excel column

On Wednesday, August 14, 2013 11:33:26 AM UTC-5, Claus Busch wrote:
Hi, Am Wed, 14 Aug 2013 09:20:32 -0700 (PDT) schrieb :
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. -- Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2


Yes, oSheet was defined as
Excel.WorkSheet oSheet = EXL.Sheet["Shhet1"];
and still received the error.

Thanks again for your help.


Sheet is a workbook object. Workbook is an Excel object. So...

Dim wksDVList, wksTarget, wkbSource, rngDVList, vDVList
Set wkbSource = appXL.Workbooks("MyWkb")
Set wksDVList = wkbSource.Sheets("DVList")
Set wksTarget = wkbSource.Sheets("Target")
Set rngDVList = wksTarget.Cells(1, 1)
vDVList = rngDVList.Validation.Formula1

...will give you the contents of the dropdown list. This will be either
a hard-entered delimited string which you'll have to determine what
delimiter is used, OR a ref to a range where the list is stored. (See
my other replies for more about the complexities involved in evaluating
the range ref)


--
Garry

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


  #25   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 24
Default Read Dropdown list from Excel column

On Wednesday, August 14, 2013 12:17:38 PM UTC-5, GS wrote:
On Wednesday, August 14, 2013 11:33:26 AM UTC-5, Claus Busch wrote: Hi, Am Wed, 14 Aug 2013 09:20:32 -0700 (PDT) schrieb : 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. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 Yes, oSheet was defined as Excel.WorkSheet oSheet = EXL.Sheet["Shhet1"]; and still received the error. Thanks again for your help. Sheet is a workbook object. Workbook is an Excel object. So... Dim wksDVList, wksTarget, wkbSource, rngDVList, vDVList Set wkbSource = appXL.Workbooks("MyWkb") Set wksDVList = wkbSource.Sheets("DVList") Set wksTarget = wkbSource.Sheets("Target") Set rngDVList = wksTarget.Cells(1, 1) vDVList = rngDVList.Validation.Formula1 ...will give you the contents of the dropdown list. This will be either a hard-entered delimited string which you'll have to determine what delimiter is used, OR a ref to a range where the list is stored. (See my other replies for more about the complexities involved in evaluating the range ref) -- Garry Free uenet access at http://www.eternal-september.org Classic VB Users Regroup comp.lang.basic.visual.misc microsoft.public.vb.general.discussion


Hi Claus,

Appreciate your help very much. However, your code does not work. The error message is the same as what I mentioned earlier. I understood workBook and WorkSheet. Yes, dropdown list is on workSheet (your wksTarget here) and the data to make the lidt is on another Sheet (your wksDVList here). I am not sure what I miss. I can email you my excel file for you to take a look if you like.
Thanks again.


  #26   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Read Dropdown list from Excel column

Hi,

Am Wed, 14 Aug 2013 09:43:09 -0700 (PDT) schrieb :

Yes, oSheet was defined as
Excel.WorkSheet oSheet = EXL.Sheet["Shhet1"];
and still received the error.


perhaps you see it better in a workbook.
Look he
https://skydrive.live.com/#cid=9378A...121822A3%21326
for the workbook "Validation" and rightclick and download it


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
  #27   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 24
Default Read Dropdown list from Excel column

On Wednesday, August 14, 2013 2:12:19 PM UTC-5, Claus Busch wrote:
Hi, Am Wed, 14 Aug 2013 09:43:09 -0700 (PDT) schrieb : Yes, oSheet was defined as Excel.WorkSheet oSheet = EXL.Sheet["Shhet1"]; and still received the error. perhaps you see it better in a workbook. Look he https://skydrive.live.com/#cid=9378A...121822A3%21326 for the workbook "Validation" and rightclick and download it Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2


Yes, I did downloaded your Excel file of "Validation". Yours lists are made up from the range on the same sheet. The list on my excel file is made up from another sheet. I still don't get why the code does not work with my file. As I said.

oSheet.Range("A1").Validation.Formula1 generated the error. I tried Formula2 and received the same error.
  #29   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Read Dropdown list from Excel column

Hi,

Am Wed, 14 Aug 2013 09:43:09 -0700 (PDT) schrieb :

Yes, oSheet was defined as
Excel.WorkSheet oSheet = EXL.Sheet["Shhet1"];
and still received the error.


perhaps you see it better in a workbook.
Look he
https://skydrive.live.com/#cid=9378A...121822A3%21326
for the workbook "Validation" and rightclick and download it


Regards
Claus B.


What I get from all this is an automation error is being raised because
of unqualified refs. Hard to say without knowing how oSheet is ref'd
down through.

I get no errors querying the Formula1 property whether hard-entered
delimited string OR named range ref, OR just range ref if on same
sheet. The only way I know to use a list stored on a different sheet is
to assign a global scope name to it. So...

In the Immediate Window:
?Sheets("Sheet2").Cells(1,1).Validation.Formula1
=List1

...where List1 refs A1:A5 on Sheet1. (a b c d e)

When hard-entered a delimited string on Sheet2 DV (a,b,c,d,e) it
returns the delimited string.

So if the property returns a range ref then...

Dim sDVList, vDVList, n&
sDVList = Sheets("Sheet2").Cells(1,1).Validation.Formula1 'wksTarget
vDVList = wkbSource.Range(Mid$(sDVList, 2) '2D array of the DV list
For n = LBound(vDVList) To UBound(vDVList)
MsgBox vDVList(n, 1)
Next

...which gives me these 5 MsgBox prompts...

a
b
c
d
e

When I use a local range ref (ie: =A1:A5) I get that back...
=A1:A5

If the local ref is a local scope defined name then I get that back...
=DVList

...so I guess the easiest way to test if the list is a delimited string
is to check it for common delimiters and exit the loop when found.

Likewise the named range. Check the sheet 1st for a local scope name.
If found the the parent is the sheet containing the DV cell. Otherwise
the parent is the workbook.

Use the Like function to test a local range ref...

If sDVList Like "=A1:A2" Then _
vDVList = wksTarget.Range(Mid$(sDVList, 2))

HTH

--
Garry

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


  #30   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Read Dropdown list from Excel column

Hi Garry,

Am Wed, 14 Aug 2013 16:34:46 -0400 schrieb GS:

If the local ref is a local scope defined name then I get that back...
=DVList


in version xl2007 or earlier list on other sheets has to be named with a
GLOBAL name.
xl2010 or later you can refer to a range on another sheet, like
Sheet2!A1:A10
Therefore it is important to know what xl version is used and how is
referred (with name or with a range)


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2


  #31   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Read Dropdown list from Excel column

Hi Garry,

Am Wed, 14 Aug 2013 22:43:30 +0200 schrieb Claus Busch:

Therefore it is important to know what xl version is used and how is
referred (with name or with a range)


and I don't know if validation can be opened in another language system
without problems because of the separators.


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
  #32   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Read Dropdown list from Excel column

Hi Garry,

Am Wed, 14 Aug 2013 16:34:46 -0400 schrieb GS:

If the local ref is a local scope defined name then I get that back...
=DVList


in version xl2007 or earlier list on other sheets has to be named with a
GLOBAL name.
xl2010 or later you can refer to a range on another sheet, like
Sheet2!A1:A10
Therefore it is important to know what xl version is used and how is
referred (with name or with a range)


Regards
Claus B.


Thanks, Claus! I did not know this limitation was removed in v2010. In
this case the sheetname will need to be parsed as I noted in an earlier
reply!

--
Garry

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


  #33   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Read Dropdown list from Excel column

Hi Garry,

Am Wed, 14 Aug 2013 22:43:30 +0200 schrieb Claus Busch:

Therefore it is important to know what xl version is used and how is
referred (with name or with a range)


and I don't know if validation can be opened in another language system
without problems because of the separators.


Regards
Claus B.


Validation.Formula1 is a read only property and so can be accessed via
automation in a normal fashion. What's important is to get the
qualified refs correct! IMO, this is the #1 problem with COM
automation; people don't get the object refs properly initialized and
so nothing works. (Same happens using VB6 or C++)

--
Garry

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


  #34   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 587
Default Read Dropdown list from Excel column

hi Hugh,

i made a model using a name for the validation

http://cjoint.com/?CHow1NBP9ko

isabelle

Le 2013-08-14 15:44, a écrit :


oSheet.Range("A1").Validation.Formula1 generated the error. I tried Formula2 and received the same error.

  #35   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Read Dropdown list from Excel column

Hi Garry,

Am Wed, 14 Aug 2013 12:41:25 -0400 schrieb GS:


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.


I tried it with both - values in validation and range ref. Both gives
back the values with semicolon as separator.
One time in validation I entered:
Test1;Test2;Test3
and another time I used
=myList
Semicolon is in german system the row separator. myList is vertical.


Regards
Claus B.


Interesting! I get back exactly what's in the DV refedit box. Using
your example for the named list I get back...

=myList

--
Garry

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




  #36   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 587
Default Read Dropdown list from Excel column

if the source range was named and that name was used to create the
validation
this code:

Range("A1").Validation.Formula1

returns the name of the source
then you can use it like this:

Range(Range("A1").Validation.Formula1).Copy Range("A1").Offset(0, 3)

isabelle

Le 2013-08-14 16:57, isabelle a écrit :
hi Hugh,

i made a model using a name for the validation

http://cjoint.com/?CHow1NBP9ko

isabelle

Le 2013-08-14 15:44, a écrit :


oSheet.Range("A1").Validation.Formula1 generated the error. I tried
Formula2 and received the same error.

  #37   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Read Dropdown list from Excel column

Hi Garry,

Am Wed, 14 Aug 2013 17:37:03 -0400 schrieb GS:

Interesting! I get back exactly what's in the DV refedit box.


yes, you always get back what is in the refedit box.
Hard coded the items or the range name (=myList) or the range itself
(=$H$1:$H$5)
You can check for the "=" to see if you can work with the items at once
or you have to work with the ranges.

But xl2010 or xl2013 you could get e.g. =Sheet2!myList or
=Sheet3!$A$1:$A$10 and you have to do a little workaround


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
  #39   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Read Dropdown list from Excel column

Hi Garry,

Am Wed, 14 Aug 2013 17:37:03 -0400 schrieb GS:

Interesting! I get back exactly what's in the DV refedit box.


yes, you always get back what is in the refedit box.
Hard coded the items or the range name (=myList) or the range itself
(=$H$1:$H$5)
You can check for the "=" to see if you can work with the items at once
or you have to work with the ranges.

But xl2010 or xl2013 you could get e.g. =Sheet2!myList or
=Sheet3!$A$1:$A$10 and you have to do a little workaround


Regards
Claus B.


Yes, I agree! I'm currently working on a reusable wrapper function that
will process the Formula1 content based on hard-entered list or range
ref. I'll post it for anyone interested to have 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


  #40   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 24
Default Read Dropdown list from Excel column

On Wednesday, August 14, 2013 5:06:55 PM UTC-5, isabelle wrote:
if the source range was named and that name was used to create the validation this code: Range("A1").Validation.Formula1 returns the name of the source then you can use it like this: Range(Range("A1").Validation.Formula1).Copy Range("A1").Offset(0, 3) isabelle Le 2013-08-14 16:57, isabelle a écrit : hi Hugh, i made a model using a name for the validation http://cjoint.com/?CHow1NBP9ko isabelle Le 2013-08-14 15:44, a écrit : oSheet.Range("A1").Validation.Formula1 generated the error. I tried Formula2 and received the same error.


Hi Isabelle,

It looks like that the dropdown List on my file is not named.
I ran your model and irt worked well. and I did see "ValList". I ran my simple code of

string s = oSheet.Range["A1"].Balidation.Formula1;
agaist your file and received "ValList", and received the error message of Formula1 not found. I know that you already helped me a lot, any more suggestions?

Hugh
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to read the contents of an OLEObject (dropdown list) in excel FurRelKT Excel Programming 0 August 3rd 10 06:01 PM
Creating a dropdown list within a column dsebiz Excel Discussion (Misc queries) 4 March 19th 08 06:56 PM
Dropdown List that populates another column HAZBONES Excel Worksheet Functions 7 December 1st 07 04:10 AM
Dropdown List - list item endings not visible if column too narrow AK9955 Excel Discussion (Misc queries) 2 April 27th 07 09:02 AM
Procedure to make a dropdown list larger so it's easier to read Jack Oziel Excel Discussion (Misc queries) 4 December 26th 05 06:49 PM


All times are GMT +1. The time now is 02:50 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"