Insert cell data in an Excel file using OLEDB in VB.net

By: Issac Viewed: 153578 times  Printer Friendly Format    


In this article we can see how to insert data into Excel file, without using Excel Object we can insert, edit, delete, select etc. in cell content of an Excel 2007 file using OLEDB in VB.NET 2005. Here we are using OleDbConnection, OleDbDataAdapter, and DataSet for doing these operations in an Excel file. You have to import System.Data in the project for doing these operations. For add new content in the cell or insert a new content, we can use the INSERT command like in SQL Operations.

sample UPDATE sql
sql = "Insert into [Sheet1$] (id,name) values('5','e')" 
The follwoing picture shows before and after update of the Sheet.

vb.vb.net_excel_insert_row_oledb.JPG

Imports System.Data

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, _
                ByVal e As System.EventArgs) Handles Button1.Click
        Try

            Dim MyConnection As System.Data.OleDb.OleDbConnection
            Dim myCommand As New System.Data.OleDb.OleDbCommand
            Dim sql As String

            MyConnection = New System.Data.OleDb.OleDbConnection _
            ("provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + _
            "'c:\testfile.xls';Extended Properties=Excel 8.0;")

            MyConnection.Open()
            myCommand.Connection = MyConnection
            sql = "Insert into [Sheet1$] (id,name) values('5','e')"
            myCommand.CommandText = sql
            myCommand.ExecuteNonQuery()
            MyConnection.Close()

        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

        MsgBox("Row Added ")

    End Sub

End Class
When you execute this source code it will insert a row in the Excel file.


Most Viewed Articles (in VB.net )

Latest Articles (in VB.net)

Comment on this tutorial

Subscribe to Tutorials

Related Tutorials

Archived Comments

1. Hi,
I've tried this but got a problem. Firs

View Tutorial          By: Hendrik Jabs at 2009-12-10 02:46:13

2. Have you tried checking for the latest office/exce
View Tutorial          By: Pat at 2010-01-07 04:54:31

3. insert a value in excel is not a problem but inser
View Tutorial          By: durga at 2010-03-05 23:08:02

4. Hi,,
I dont have a problem Insert, Update a

View Tutorial          By: Yavuz at 2010-09-01 14:15:02

5. This works with inserts but it is very slow for lo
View Tutorial          By: Antonio Pereira at 2011-02-04 08:54:45

6. Hi,
I wann to insert data into excel file b

View Tutorial          By: PRUTHVIRAJ RATHOD at 2011-05-23 11:42:14

7. HI everyone,
I want to select par

View Tutorial          By: aarthi.s at 2011-09-06 10:09:16

8. really nice tutorial
View Tutorial          By: Gaurav Balyan at 2012-01-03 11:07:05

9. can anyone please provide me how to map the column
View Tutorial          By: Gaurav Balyan at 2012-01-03 11:09:26

10. This code worked but it only add the same vales se
View Tutorial          By: Dammy Temmy at 2013-01-17 17:22:17

11. coumadin valtrex interaction debcgddgcdfdeckb
View Tutorial          By: Smithc104 at 2016-08-15 01:22:13

12. Billyweimb
View Tutorial          By: Billyweimb at 2017-05-16 14:54:53

13. Billyweimb
View Tutorial          By: Billyweimb at 2017-07-17 11:18:05