We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Reading Excel cell into Combo box or array in VB?

holemania
holemania asked
on
Medium Priority
319 Views
Last Modified: 2010-05-01
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
Comment
Watch Question

Wim
CERTIFIED EXPERT

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

Author

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?
Wim
CERTIFIED EXPERT

Commented:
What type of values are ther in B1-10? Integer, String, ...
Wim
CERTIFIED EXPERT

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

Author

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....
CERTIFIED EXPERT
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

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
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.