Update cell data in an Excel file using OLEDB in VB.net
By: Issac
In this article we can see how to update an cell in a Excel file using OleDB connection and DataAdapter and using UPDATE command
sample UPDATE sql
sql = "Update [Sheet1$] set name = 'New Name' where id=1"
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 = "Update [Sheet1$] set name = 'New Name' where id=1"
myCommand.CommandText = sql
myCommand.ExecuteNonQuery()
MyConnection.Close()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
MsgBox("Updated ")
End Sub
End Class
When you execute this source code the will update the specified content.
Archived Comments
1. string strInsert = "Insert into [Sheet1$](F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12,F13,F14,F15,F1
View Tutorial By: Sagar at 2011-05-12 06:47:13
2. how to append data to existing excel sheets without loosing any data using jxl??
View Tutorial By: nim at 2011-03-23 00:28:10
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
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