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

By: Issac Emailed: 1699 times Printed: 2200 times    

Latest comments
By: rohit kumar - how this program is work
By: Kirti - Hi..thx for the hadoop in
By: Spijker - I have altered the code a
By: ali mohammed - why we use the java in ne
By: ali mohammed - why we use the java in ne
By: mizhelle - when I exported the data
By: raul - no output as well, i'm ge
By: Rajesh - thanx very much...
By: Suindu De - Suppose we are executing

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.

VB.net Home | All VB.net Tutorials | Latest VB.net Tutorials

Sponsored Links

If this tutorial doesn't answer your question, or you have a specific question, just ask an expert here. Post your question to get a direct answer.



Bookmark and Share

Comments(10)


1. View Comment

Hi,
I've tried this but got a problem. First I run a 'create table'- command to define my columns and datatypes in the excel file.
Then I tried to insert a datatable to the excel file by running an 'insert'- command for each row of the datatable. The excel- sheet only contains the header and the last row of my datatable after that. It seems that the insert command starts to write always in the second row.
How can I solve that problem?
cu


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

Have you tried checking for the latest office/excel service packs and hotfixes?

View Tutorial          By: Pat at 2010-01-07 04:54:31
3. View Comment

insert a value in excel is not a problem but insert a textbox is giving is error the error is in the value specified value is requried

View Tutorial          By: durga at 2010-03-05 23:08:02
4. View Comment

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

Error : Deleting data in a linked table is not supported by this ISAM.
Have a idea?


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

This works with inserts but it is very slow for long records. Any hint about how could it be hurried a little bit more? (sorry my main language is not english)

View Tutorial          By: Antonio Pereira at 2011-02-04 08:54:45
6. View Comment

Hi,
I wann to insert data into excel file but not in static way. I want insert data from textbox or variable of form please help me.


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

HI everyone,
I want to select particular row excel data by getting text from textbox which is in .net form.I have added gridview ,bindingsource,textbox,button.i m able to select whole data which is in excel sheet but not able to select particular row that matches the text which i was created a textbox.i request u to send me query/coding.plz anyone can help me out.


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

really nice tutorial

View Tutorial          By: Gaurav Balyan at 2012-01-03 11:07:05
9. View Comment

can anyone please provide me how to map the columns from the sql server to the columns in the excelsheet , i want to export the data from the sql server table to the excelsheet by the use of column mappings

View Tutorial          By: Gaurav Balyan at 2012-01-03 11:09:26
10. View Comment

This code worked but it only add the same vales set in the code but I want to add values by calling the values from a textbox. How do I do this please

View Tutorial          By: Dammy Temmy at 2013-01-17 17:22:17

Your name (required):


Your email(required, will not be shown to the public):


Your sites URL (optional):


Your comments:



More Tutorials by Issac
Save/Write/Read image file from/to a database using Java program
How to modify the objects using java classes
Java program for Cloning
Java program for changeable wrapper class
A tutorial on Chat Server and Chat Client in VB.net
Chat Server in VB.net
Chat client in VB.net
How to open and read an XML file in VB.net
How to create an XML file in VB.net
XML and VB.net
How to create an XML file in VB.NET using Dataset
Multi Threaded Client Socket Programming in VB.net
Multi Threaded Server Socket Programming in VB.net
Multi threaded Socket Programming in VB.net
Client Socket Program sample in VB.net

More Tutorials in VB.net
Scope in VB.net
Unstructured Exception Handling in VB.net
Structured Exception Handling in VB.net
Creating Sub Procedures in VB.net
Creating Functions in VB.net
Passing a Variable Number of Arguments to Procedures in VB.net
Specifying Optional Arguments with default values in Procedures in VB.net
Preserving a Variable's Values between Procedure Calls in VB.net
Procedure Delegates in VB.net
Properties in VB.net
Understanding Scope in VB.net
Using Resume Next and Resume Line in VB.net
Using On Error GoTo 0 in VB.net
Getting an Exception's Number and Description in VB.net
Raising an Exception Intentionally in VB.net

More Latest News
Most Viewed Articles (in VB.net )
File Extensions Used in VB .NET
Your first VB.NET Crystal Reports - A step by step guide
Converting between Data Types in VB.net
Arrays and Dynamic Arrays in VB.net
For Loop in VB.net
How to export from DataGridView to excel using VB.net
How to create an XML file in VB.NET using Dataset
A tutorial on Chat Server and Chat Client in VB.net
Passing a Variable Number of Arguments to Procedures in VB.net
Preserving a Variable's Values between Procedure Calls in VB.net
Debug and Release Versions in .NET
Using Select Case in VB.net
Do Loop in VB.net
For Each…Next Loop in VB.net
File stream operations in VB.net
Most Emailed Articles (in VB.net)
How to export from database to excel using VB.net
What is .NET Framework and the Common Language Runtime?
Create an Excel 2007 file in VB.NET 2005
Insert Pictures in Excel 2007 from VB.NET 2005
Socket Programming in VB.net
Chat Server in VB.net
Using the ServiceBase Class in VB.net
Changes in Controls from VB6 to VB.net
While Loop in VB.net
String Insert, index off & format in VB.net
Substring in Vb.Net String Class
String Split function in VB.net
Vb.net text reader
Using Excel 2007 Chart VB.net Picture Box
Using Excel 2007 DataBar in VB.net