3 Replies - 1756 Views - Last Post: 26 June 2013 - 03:55 PM Rate Topic: -----

#1 jmazorra  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 26-June 13

Binding a combobox to a column in Excel using Vb.net

Posted 26 June 2013 - 02:15 PM

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





Is This A Good Question/Topic? 0
  • +

Replies To: Binding a combobox to a column in Excel using Vb.net

#2 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3608
  • View blog
  • Posts: 12,393
  • Joined: 12-December 12

Re: Binding a combobox to a column in Excel using Vb.net

Posted 26 June 2013 - 03:22 PM

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

Was This Post Helpful? 0
  • +
  • -

#3 jmazorra  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 26-June 13

Re: Binding a combobox to a column in Excel using Vb.net

Posted 26 June 2013 - 03:40 PM

View Postandrewsw, 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

Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3608
  • View blog
  • Posts: 12,393
  • Joined: 12-December 12

Re: Binding a combobox to a column in Excel using Vb.net

Posted 26 June 2013 - 03:55 PM

Dim dt As New DataTabel

You've misspelt Table.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1