SiteMap  | About    

Update cell data in an Excel file using OLEDB

Without using Excel Object we can insert , edit , delete , select etc. in cell content of an Excel file using OLEDB in VB.NET . Here we are using OleDbConnection , OleDbDataAdapter , DataSet for doing these operations in an Excel file. You have to import System.Data in the project for doing these operations . For update the content in the cell or modify the content in a cell , We can use the UPDATE command like in SQL Operations.

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
            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;")

            myCommand.Connection = MyConnection
            sql = "Update [Sheet1$] set name = 'New Name' where id=1"
            myCommand.CommandText = sql
        Catch ex As Exception
        End Try
        MsgBox("Updated ")
    End Sub
End Class

When you execute this source code the will update the corrosponding content. (C) 2017    Founded by raps mk
All Rights Reserved. All other trademarks are property of their respective owners.