[jmazorra]

Hello:

I have embedded a combobox on Sheet dsbPositionBoard. I want to populate that combobox with the contents of column A6:A from refAllEmployeesAnnualized sheet.

So far, this is what I have worked out, but I am stuck and can't get it to work.

Imports System.Data.OleDb
Imports Microsoft.Office.Interop.Excel
Imports Microsoft.Office.Interop

Public Class dsbPositionBoard

    Private Sub dsbPositionBoard_Startup() Handles Me.Startup

        Using MyConnection As New System.Data.OleDb.OleDbConnection()

            MyConnection.Open()


            Dim da As New OleDbDataAdapter("SELECT DISTINCT * FROM [refAllEmployeesAnnualized$A6:A]", MyConnection)

            Dim dt As New DataTable

            da.Fill(dt)

            cmbSelectPosition.DisplayMember = dt.Columns(0).ColumnName
            cmbSelectPosition.DataSource = dt

        End Using

    End Sub

[andrewsw]

You should tell us if there is an error. If there is, then post it together with confirmation as to which line of your code it refers to.

However, $A6:A is not a valid range, you need to specify the end-row:

"SELECT DISTINCT * FROM [refAllEmployeesAnnualized$A1:A1000]"

You may be confusing the syntax with A:A which identifies the whole column.

If you don't know the end-row number then you'll need to use Excel (Rows.Count) to find this number and concatenate this to your sql-statement.

This post has been edited by andrewsw: 26 June 2013 - 03:23 PM

[jmazorra]

andrewsw, on 26 June 2013 - 03:22 PM, said:

You should tell us if there is an error. If there is, then post it together with confirmation as to which line of your code it refers to.

However, $A6:A is not a valid range, you need to specify the end-row:

"SELECT DISTINCT * FROM [refAllEmployeesAnnualized$A1:A1000]"

You may be confusing the syntax with A:A which identifies the whole column.

If you don't know the end-row number then you'll need to use Excel (Rows.Count) to find this number and concatenate this to your sql-statement.

Thank you for the reply. I had not noticed the syntax identifying the column. The error that I am getting is: New cannot be used on an interface.

This on line:

Dim dt As New DataTabel

[andrewsw]

Dim dt As New DataTabel

You've misspelt Table.