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

By: Issac Emailed: 1728 times Printed: 2329 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 )
Your first VB.NET Crystal Reports - A step by step guide
OleDbConnection class in VB.net
Creating a Windows Service Installer in VB.net
Unstructured Exception Handling in VB.net
What is .NET Framework and the Common Language Runtime?
Assemblies, Solutions and Projects in VB .NET
The Option and Imports Statements in VB .NET
Arrays and Dynamic Arrays in VB.net
Handling Strings in VB.net
Operators in VB.net
For Loop in VB.net
For Each…Next Loop in VB.net
Handling Dates and Times in VB.net
How To Connect To A MySql Database in VB.net
Excel 2007 Data Validation Input Message using VB.net
Most Emailed Articles (in VB.net)
“Using If with And” and Comparing two integers using If
Arrays and Dynamic Arrays in VB.net
Using Select Case in VB.net
How to open and read an XML file in VB.net
Creating Functions in VB.net
Throwing an Exception in VB.net
Throwing a Custom Exception in VB.net
Nested If and Single line if statement
The Select Case statement in VB.net
The For Loop in VB.net
What's New in VB .NET? A comparison of VB vs VB.net
What is .NET Framework and the Common Language Runtime?
Debug and Release Versions in .NET
File Extensions Used in VB .NET
Assemblies, Solutions and Projects in VB .NET