microsoft excel

[aadi_capri]

hello friends,
i have a windows project in vb.net, in which i have to connect my project with an excel database i have microsoft office 2007 installed on my lappy..
please help me with the code.
thanks in advance..

[adam_k]

Read here: http://vb.net-informations.com/excel-2007/vb.net_excel_oledb.htm
or here: http://www.codeproject.com/KB/vb/Excel_Connectivity.aspx

[aadi_capri]

and how can we retrive a string from an excel database in the dataset?

[Pgmer]

Try
            Dim fBrowse As New OpenFileDialog

            With fBrowse
                .Filter = "Excel files(*.xls)|*.xls|All files (*.*)|*.*"
                .FilterIndex = 1
                .Title = "Import data from Excel file"
            End With
            If fBrowse.ShowDialog() = Windows.Forms.DialogResult.OK Then
                Dim fname As String
                fname = fBrowse.FileName
                Dim MyConnection As System.Data.OleDb.OleDbConnection
                Dim DtSet As System.Data.DataSet
                Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
                MyConnection = New System.Data.OleDb.OleDbConnection _
                ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & fname & ";" & "Extended Properties=""Excel 8.0;HDR=NO;IMEX=1""")
                MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from[Sheet1$]", MyConnection)
               


                MyCommand.TableMappings.Add("Table", "TestTable")
                DtSet = New System.Data.DataSet
'' Here is dataset with all the data from Excel file.
                MyCommand.Fill(DtSet)
                MyConnection.Close()
            End If


        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try

[aadi_capri]

dbprovider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 5.0;"
dbsource = "Data Source = C:\Documents and Settings\aditya\My Documents\user2.XLS"

con.ConnectionString = dbprovider & dbsource
con.Open()
SQL = "SELECT * FROM [products$]"
Cmd = New OleDb.OleDbCommand(SQL, con)
objCmd = New OleDb.OleDbCommand(SQL, con)
objCmd.ExecuteNonQuery()
da = New OleDb.OleDbDataAdapter(SQL, con)
'MsgBox("hello connection is opened")
da.Fill(ds, "AddressBook")
con.Close()
DataGrid1.DataSource = ds
DataGrid1.SetDataBinding(ds, "addressbook")

i have used this code and its working good so far but its not able to retrive a string into the datagrid, its giving a value as null, where the text,"hello" is present in the excel sheet.

[Pgmer]

In which column the text is? And can you provide the sample excel file? It should read whaterver is there in excel file.

[adam_k]

Why do you have these:

objCmd = New OleDb.OleDbCommand(SQL, con)
objCmd.ExecuteNonQuery()

SQL var does contain a query.

And I don't think you are going to need this: Cmd = New OleDb.OleDbCommand(SQL, con) either.

[Reverend Jim]

Is it an actual database (regularly definied rows and columns) or is it a spreadsheet with varying fields depending on the row? In one case you CAN access the data using ADO (or whatever MS calls it now) and recordsets, etc. If not then you can access the rown and columns using an Excel object that can be created within VB. If it's the second case we can also give you sample code.