asked on

Fill combo box from Excel column.

I need to be able to select an employee's last name from the A column in Excel. I don't get any errors the way it is right now but it doesn't work, the combo box is empty. I put in "A1" to try to get at least something. Thank You!

Private Sub cboEmployee_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboEmployee.SelectedIndexChanged
        Dim objExcel As New Microsoft.Office.Interop.Excel.Application   ' Represents an instance of Excel
        Dim objWorkbook As Microsoft.Office.Interop.Excel.Workbook     'Represents a workbook object
        Dim objWorksheet As Microsoft.Office.Interop.Excel.Worksheet     'Represents a worksheet object
        Dim LastRow As Double
 
        'makes excel invisible to the user
        objExcel.Visible = False
 
        'open existing excel file
        objWorkbook = objExcel.Workbooks.Open("C:\Program Files\Payroll\Employees.xls")
 
        'use the first worksheet
        objWorksheet = objWorkbook.Worksheets(1)
 
        'finds last row with data by jumping to last possible row in excel (65536) then jumping back up to first data found
        LastRow = objWorksheet.Range("a65536").End(Microsoft.Office.Interop.Excel.XlDirection.xlUp).Row
 
        cboEmployee.Items.Add(objWorksheet.Range("A1", LastRow))
        cboEmployee.SelectedIndex = -1
    End Sub

Open in new window

Jorge Paulino

Hi scottkern3,

Try to replace:
cboEmployee.Items.Add(objWorksheet.Range("A1", LastRow))
With:
cboEmployee.Items.Add(objWorksheet.Range("A1", "A" & LastRow))

scottkern3

ASKER

Still the same. Thanks for helping.

I have a question... so would this fill in the names when I click the dropdown button or when I launch the program? or should it matter? Thanks

Jorge Paulino

If you don't have any item on the combobox this will never fill the values. If this is the case then move this to form load event and tell me something.

scottkern3

ASKER

Excuse my ignorance but where is the load event? Just below the "Windows Form Designer code"?

Jorge Paulino

There's a lot of ways to get it.
- Select the form (not any object) and on the properties windows select the events an choose for load;
- Double click on the form (not any object)
- On code windows select the left combo an choose Form Events and then on the right choose load

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

End Sub

scottkern3

ASKER

"System.__ComObject" is in the combo box now. Thats the only selection. Now were getting somewhere.

scottkern3

ASKER

I was trying to view the employee.xls file and it says it's locked for editing and that I could view a read only version. My payroll program isn't running...could this be the issue?

Jorge Paulino

>> I was trying to view the employee.xls file and it says it's locked for editing and that I could view a read only version. My payroll program isn't running...could this be the issue?
No. Go to task manager and close on excel instances that you have. You're not closing the excel in the end of the routine.

Give me some minutes to test it.

ASKER CERTIFIED SOLUTION

Jorge Paulino

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

scottkern3

ASKER

Wow! Nice job! Thank you JPaulino! Works like a champ.

scottkern3

ASKER

Thank you so much!