?
Solved

Reading Excel cell into Combo box or array in VB?

Posted on 2006-04-06
7
Medium Priority
?
304 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
0
Comment
Question by:holemania
  • 4
  • 3
7 Comments
 
LVL 4

Expert Comment

by:Wim
ID: 16392571
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
 

Author Comment

by:holemania
ID: 16393323
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
 
LVL 4

Expert Comment

by:Wim
ID: 16394441
What type of values are ther in B1-10? Integer, String, ...
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Expert Comment

by:Wim
ID: 16398779
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
 

Author Comment

by:holemania
ID: 16400826
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
 
LVL 4

Accepted Solution

by:
Wim earned 500 total points
ID: 16412264
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
 

Author Comment

by:holemania
ID: 16417556
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month13 days, 10 hours left to enroll

750 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question