holemania
asked on
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
Set ws = wb.Sheets("sheet1")
For iRow = 11 To 13
cboModes1.AddItem ws.Cells(iRow, 1)
cboModes1.ItemData(cboMode
Next iRow
Set xl = Nothing
Set wb = Nothing
Set ws = Nothing
End Sub
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
Set ws = wb.Sheets("sheet1")
For iRow = 11 To 13
cboModes1.AddItem ws.Cells(iRow, 1)
cboModes1.ItemData(cboMode
Next iRow
Set xl = Nothing
Set wb = Nothing
Set ws = Nothing
End Sub
ASKER
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(cboMode
This line ' cboModes1.ItemData(cboMode
What type of values are ther in B1-10? Integer, String, ...
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.OL
'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.
rsdata.MoveNext
Loop
End Sub
Private Sub cboModes1_Click()
rsdata.MoveFirst
rsdata.Move cboModes1.ItemData(cboMode
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
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.OL
'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.
rsdata.MoveNext
Loop
End Sub
Private Sub cboModes1_Click()
rsdata.MoveFirst
rsdata.Move cboModes1.ItemData(cboMode
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
ASKER
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....
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....
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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
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(cboMode
Next iRow
Set xl = Nothing
Set wb = Nothing
Set ws = Nothing
End Sub
Private Sub cboModes1_Click()
Text1.Text = cboModes1.ItemData(cboMode
End Sub
Greetz,
Wim