Programming Tutorials

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