Reading Excel cell into Combo box or array in VB?

I have a VB application in which I'm able to read Excel cell range into a combo box.  The issue is that in cell A1 I have the category, and B1 is the description.  What I want to be able to do is if a user selects an item from the combo box, the appropriate description displays.  The only way I can think of is using an array to read from the Excel file and then populate the combo box from the array.  Within the combo box, depending on what is selected will pull the description from the combo box into the text box next to it.  The problem is I'm not sure how I would go about coding this.  Anyone can provide an example?   I got a sample below which reads from an Excel file into a combo, so not sure if I can just tweak this code to do what I wanted.

Private Sub Form_Load()

Dim xl As New Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim iRow As Integer

Set wb = xl.Workbooks.Open("C:\Spec.xls")
Set ws = wb.Sheets("sheet1")


For iRow = 11 To 13
    cboModes1.AddItem ws.Cells(iRow, 1)
    cboModes1.ItemData(cboModes1.ListCount - 1) = ws.Cells(iRow, 13)
Next iRow

Set xl = Nothing
Set wb = Nothing
Set ws = Nothing

End Sub
holemaniaAsked:
Who is Participating?
 
WimCommented:
maybe if you change your form_load to this:

Private Sub Form_Load()
    Set conn = New ADODB.Connection
    Set rsdata = New ADODB.Recordset
    conn.CursorLocation = adUseClient
    conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Spec.xls;Extended Properties=""Excel 8.0;HDR=Yes" & """"
                   'HDR=Yes ==> first line in excel is the header, if you don't have a header change to "No"
    rsdata.Open "SELECT * FROM [Sheet1$]", conn, adOpenDynamic, adLockOptimistic
    Do Until rsdata.EOF
        if not isnull(rsdata.fields(0)) then
             cboModes1.AddItem rsdata.Fields(0)
             cboModes1.ItemData(rsdata.AbsolutePosition - 1) = rsdata.AbsolutePosition
        endif
        rsdata.MoveNext
     Loop
End Sub
0
 
WimCommented:
This should do the trick:

Private Sub Form_Load()
Dim xl As New Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim iRow As Integer

Set wb = xl.Workbooks.Open("C:\Spec.xls")
Set ws = wb.Sheets("sheet1")


For iRow = 1 To 10
    cboModes1.AddItem ws.Cells(iRow, 1)                                                   'adds cell A1 to A10 to your combolist
    cboModes1.ItemData(cboModes1.ListCount - 1) = ws.Cells(iRow, 2)         'adds cell B1 to B10 to your combodata
Next iRow

Set xl = Nothing
Set wb = Nothing
Set ws = Nothing

End Sub

Private Sub cboModes1_Click()
    Text1.Text = cboModes1.ItemData(cboModes1.ListIndex)                        'puts the combodata in de textbox
End Sub


Greetz,
Wim
0
 
holemaniaAuthor Commented:
Thanks Wim.  I think that might do it, but now i'm running into another issues.  Now that I filled in Cell B1-10 and i'm getting a 'Run-time error 13: Type mismatch'.  Before I added in values to the cell B column it was working but now that I added in values to cell B1 and on it's giving me error.  

This line ' cboModes1.ItemData(cboModes1.ListCount - 1) = wsCells(iRow, 2) ' is the on that it errors out on.  Any ideas?
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
WimCommented:
What type of values are ther in B1-10? Integer, String, ...
0
 
WimCommented:
ItemData must be a long integer ... so it won't work if there's text in B1-10.

maybe it's better to open your excel into a recordset:

general declarations:
Dim rsdata As ADODB.Recordset
Dim conn As ADODB.Connection

Private Sub Form_Load()
    Set conn = New ADODB.Connection
    Set rsdata = New ADODB.Recordset
    conn.CursorLocation = adUseClient
    conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Spec.xls;Extended Properties=""Excel 8.0;HDR=Yes" & """"
                   'HDR=Yes ==> first line in excel is the header, if you don't have a header change to "No"
    rsdata.Open "SELECT * FROM [Sheet1$]", conn, adOpenDynamic, adLockOptimistic
    Do Until rsdata.EOF
        cboModes1.AddItem rsdata.Fields(0)
        cboModes1.ItemData(rsdata.AbsolutePosition - 1) = rsdata.AbsolutePosition
        rsdata.MoveNext
     Loop
End Sub

Private Sub cboModes1_Click()
    rsdata.MoveFirst
    rsdata.Move cboModes1.ItemData(cboModes1.ListIndex - 1)
    Text1.Text = rsdata.Fields(1)
End Sub

Private Sub Form_Unload(Cancel As Integer)
    rsdata.Close
    conn.Close
    Set rsdata = Nothing
    Set conn = Nothing
End Sub
0
 
holemaniaAuthor Commented:
Value for B1-10 consists of long descriptions.  So that might be the case why it's not working.

With your above example, it says that I have Null value and then errors out.  Is it possible to specify the range instead using end of file?  This Excel spreadsheet consist of a number of values ranging from A1-10, and then A12-A20, etc....
0
 
holemaniaAuthor Commented:
Awesome.  Thanks for the help.  

I was able to specify the range from the Select Query to get from Cell A10-20.  Just in case any one might be wondering, you can specifiy the Cell range by going Selecting the cells and then go to Insert-->Name--> Define and type in the name for that range.

Example:

SELECT * FROM myRange
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.