VB.Net connect to Excel

[agibsonsw]

Hello. Visual Basic 2010 Express and Excel 2007.
I want to use VB 2010 to connect to an Excel data source. I'll probably end up doing it in a few different ways but I just need a few pointers to get me started.

There are various Data controls: BindingSource, DataGridView, etc. If I add a DataGridView and try to add a data source using the "Add Project Data Source" option it only offers me Access or SQL database sources. If I separately visit the Data Source pane the same restricted options are available.

Would I be right to assume that I need to programmatically create a connection to Excel and set properties for the DataGridView? Which specific properties would I need to set pl? Perhaps I even need to dynamically show, or create, the DataGridView itself? I believe the connection string would be:

Private m_sConn1 As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:\ExcelData1.xls;" & _
"Extended Properties=""Excel 12.0 Xml;HDR=YES"""
and I would be using ADO.Net.

If I didn't use the DataGridView could I use the "BindingSource" or "DataSet" controls? I presume I could then (somehow..) bind TextBoxes, etc., to Excel fields?

Thanks in advance for any pointers

[HansV]

See Connection strings for Excel 2007 for examples of ADO connection strings.

[agibsonsw]

See Connection strings for Excel 2007 for examples of ADO connection strings.

Thank you Hans, although the connection string didn't prove to be a problem - my working code is below.

I can connect using ADO.Net and I'm sure I will be able to do this with ADO and automate Excel with VBA (from VB.Net). The new subject for me is using the Data controls: DataGridView, BindingSource and DataSet.

Specifically, how do I populate the DataGridView with Excel source data? Andy.

Code: Select all

Imports System.Data.OleDb

Public Class frmExcel
    Private m_sConn1 As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
               "Data Source=C:\Users\Andrew\Documents\AndysData7.xlsx;" & _
               "Extended Properties=""Excel 12.0 Xml;HDR=YES"""     'IMEX=1 would treat all columns as text

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

    End Sub

    Private Sub btnConnectExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnConnectExcel.Click
        Dim conn1 As New System.Data.OleDb.OleDbConnection(m_sConn1)
        conn1.Open()
        Dim cmd1 As New System.Data.OleDb.OleDbCommand("SELECT * FROM staffData", conn1)
        Dim rdr As OleDbDataReader = cmd1.ExecuteReader
        Do While rdr.Read()
            Debug.WriteLine(rdr.GetValue(0))
        Loop
        rdr.Close()
        conn1.Close()

    End Sub
End Class

[agibsonsw]

I managed to get this to work - to populate the DataViewGrid from an Excel file - with a bit of Googling around and educated (sort of..) guesswork.

I used a DataSet and DataAdaptor rather than a DataReader. I understand it's also possible to convert a DataReader to a DataSet, but I suppose it would help if I knew what these things were . Bit of study required!

Still curious about the purpose and use of the BindingSource Data control(?) but I'm sure I'll come across it in my studies. I'm guessing that this and the DataSet control are non-programmatic ways to achieve what I've done in code. Andy.

Code: Select all

Imports System.Data.OleDb

Public Class frmExcel
    Private m_sConn1 As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
               "Data Source=C:\Users\Andrew\Documents\AndysData7.xlsx;" & _
               "Extended Properties=""Excel 12.0 Xml;HDR=YES"""     'IMEX=1 would treat all columns as text

    Private Sub btnConnectExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnConnectExcel.Click
        Dim conn1 As New System.Data.OleDb.OleDbConnection(m_sConn1)
        conn1.Open()
        Dim da As OleDbDataAdapter
        Dim ds As New DataSet
        da = New OleDbDataAdapter("SELECT * FROM staffData", conn1)
        da.Fill(ds)
        da.Dispose()
        Me.DataGridView1.DataSource = ds.Tables(0)
        conn1.Close()
    End Sub
End Class

[markjeggar]

You can connect vb.net to excel using oledb , please check the following link , it may help you.

http://csharp.net-informations.com/exce ... -oledb.htm" onclick="window.open(this.href);return false;

jiggr.