[RESOLVED] How to read the value of an excel combo box from VB?

[TheOldGuy]

I can read 'regular' cell values from Excel into a VB program (either thru a recordset or as an Excel Object). But the cell value for a combo box reads into VB from Excel as an empty string even though there is clearly a text string visible in the box. How can I get the actual string that the user selected from the combo box?

[anhn]

What is the code you use to read "the cell value for a combo box" into VB???
The task will be easier for you if you link the combobox to a cell then read the value of that cell.
However, there are 2 different kinds of combobox in Excel, which one have you used? Form combobox or ActiveX combobox?

[westconn1]

you need to get the text property of the combobox
shtobject.combobox1.text

[TheOldGuy]

Thank you for your responses. I did not set up the Excel spreadsheet I am trying to read. I can use either rs.Fields(1) with ADO or xlsheet.Cells(5, 2) with Excel Objects, but the result is the same. I can read combo boxes with single and integer values in them into my VB6 app. However, I can not read combo boxes that have string values in them from the same spreadsheet. What am I doing incorrectly? How can I get these string values?

[westconn1]

are the you reading the values into a numeric type variable?
show us some code of what you are trying to do, including declaration of relevant variables

[TheOldGuy]

Private Sub GetExcelData()
  Dim xl As New Excel.Application
  Dim xlsheet As Excel.Worksheet
  Dim xlwbook As Excel.Workbook
  Dim txt1 As String
  Dim txt2 As String
 
  strfile = "C:\Documents and Settings\" & Windowsid & "\Desktop\Windload\" & exsht
  Set xlwbook = xl.Workbooks.Open(strfile)
  Set xlsheet = xlwbook.Sheets.Item(2) 
  
  txt1 = xlsheet.Cells(5, 2).Value  'drop-down list with string values
  txt2 = xlsheet.Cells(28, 2).Value 'drop-down list with single values
  
  xl.ActiveWorkbook.Close False, strfile
  xl.Quit

  Set xlwbook = Nothing
  Set xl = Nothing
End Sub

Txt2's cell reads into VB6 app fine as "0.500", txt1's cell is "Alternate" in Excel but reads into VB6 as an empty string. Any ideas?

[Siddharth Rout]

txt1's cell is "Alternate" in Excel

Though you have mentioned it here but do this for me.

manually open the excel file and check what is the value of cell B5 in the relevant sheet?

[TheOldGuy]

As you requested, I manually opened the excel file and checked the value of the cell I'm trying to get as txt1. That particular cell is a drop-down where the excel user can select either Alternate or Continuous. The value that shows up at the top of the spreadsheet in the fx box is Alternate.

[Siddharth Rout]

Hi Can I see your excel sheet. Also you mentioned combo box in the title but you are refering to a Drop down list which is created with Data=>Validation right?

[TheOldGuy]

I am a not that familiar with excel. I said combo box because that's what it reminded me of first. I'm learning excel teminology on the fly here just to do this project. Due to company policy, I am not allowed to post the spreadsheet. I found that if I write the excel spreadsheet out to a csv file, then the text strings I need do show up correctly in that file. If I can't find a clean way to read the string directly from excel then I could do that and parse the .csv file to get what I need.

[Siddharth Rout]

I am a not that familiar with excel. I said combo box because that's what it reminded me of first. I'm learning excel teminology on the fly here just to do this project. Due to company policy, I am not allowed to post the spreadsheet. I found that if I write the excel spreadsheet out to a csv file, then the text strings I need do show up correctly in that file. If I can't find a clean way to read the string directly from excel then I could do that and parse the .csv file to get what I need.

That's ok

I actually wanted to check a few things but you can check them for me.

1) When Cells B5 and C5 are merged the ceel address becomes B5. So could you check if the cells are merged.

The value that shows up at the top of the spreadsheet in the fx box is Alternate.

2) What do you mean by "top of the spreadsheet" are you refering to the formula bar or the cell?

3) What is the name of the workbook and the worksheet?

I can think of these 3 at the moment...

Let see how it unfolds

[TheOldGuy]

1.) If by 'merged cells', you mean that when I click on B5 then the box covers B5 and C6, then yes that's true.
2.) I meant the formula bar right under the toolbars at the top of the excel screen.
3.) Workbook name is 3_OUT_STL_WL, worksheet name is Windload Calcs.

[TheOldGuy]

sorry I mis-typed.
'I click on B5 then the box covers B5 and C6' should be B5 and C5.

[Siddharth Rout]

1.) If by 'merged cells', you mean that when I click on B5 then the box covers B5 and C6, then yes that's true.

What does

txt1 = xlsheet.Cells(5, 3).Value

in the above code give you?

[TheOldGuy]

I tried that too. Still gives an empty string.

[Siddharth Rout]

okay lets try this experiment...

open the excel sheet

in the module paste this and then tell me what do you get when you run it...

Sub aaa()
'First try this
MsgBox Sheets("Windload Calcs").Cells(5, 2)

'then comment the above and then uncomment the below
'MsgBox Sheets("Windload Calcs").Cells(5, 3)

End Sub

[TheOldGuy]

MsgBox Sheets("Windload Calcs").Cells(5, 2) - showed the string I need to get

'MsgBox Sheets("Windload Calcs").Cells(5, 3) - was an empty string.

[Siddharth Rout]

Then the problem is here....

Set xlsheet = xlwbook.Sheets.Item(2)

This is not setting it to the right worksheet which is ("Windload Calcs")

Try this

Set xlsheet = xlwbook.Sheets("Windload Calcs")

[TheOldGuy]

Ok. That returns the string. Thank you ver y much. I don't understand though why it would return the value of the first column but not the value of the second column in the same row before? I certainly do appreciate your help and time on this. I will mark this as resolved (as soon as I figure out how to do that).

[Siddharth Rout]

more than that what bothers me is that with that code you could get the right values for

txt2 = xlsheet.Cells(28, 2).Value

[TheOldGuy]

Yes, that was a typo, it should have been (5, 2). Thx.