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.
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.Comment on this tutorial
- Data Science
- Android
- AJAX
- ASP.net
- C
- C++
- C#
- Cocoa
- Cloud Computing
- HTML5
- Java
- Javascript
- JSF
- JSP
- J2ME
- Java Beans
- EJB
- JDBC
- Linux
- Mac OS X
- iPhone
- MySQL
- Office 365
- Perl
- PHP
- Python
- Ruby
- VB.net
- Hibernate
- Struts
- SAP
- Trends
- Tech Reviews
- WebServices
- XML
- Certification
- Interview
categories
Subscribe to Tutorials
Related Tutorials
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
Exception Filtering in the Catch Block in VB.net
Using Multiple Catch Statements in VB.net
Throwing an Exception in VB.net
Throwing a Custom Exception in VB.net
Changes in Controls from VB6 to VB.net
Unstructured Exception Handling in VB.net
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