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

By: Issac  

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.



Archived Comments

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

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

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

4. This code worked but it only add the same vales set in the code but I want to add values by calling
View Tutorial          By: Dammy Temmy at 2013-01-17 17:22:17

5. can anyone please provide me how to map the columns from the sql server to the columns in the excels
View Tutorial          By: Gaurav Balyan at 2012-01-03 11:09:26

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

7. HI everyone,
I want to select particular row excel data by getting text from textbo

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

8. Hi,
I wann to insert data into excel file but not in static way. I want insert data from text

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

9. This works with inserts but it is very slow for long records. Any hint about how could it be hurried
View Tutorial          By: Antonio Pereira at 2011-02-04 08:54:45

10. Hi,,
I dont have a problem Insert, Update and select but ı have an error ehen Delete

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

11. insert a value in excel is not a problem but insert a textbox is giving is error the error is in the
View Tutorial          By: durga at 2010-03-05 23:08:02

12. Have you tried checking for the latest office/excel service packs and hotfixes?
View Tutorial          By: Pat at 2010-01-07 04:54:31

13. Hi,
I've tried this but got a problem. First I run a 'create table'- command to define my col

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


Most Viewed Articles (in VB.net )

Latest Articles (in VB.net)

Comment on this tutorial