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.

vb.net_excel_update_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 = "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


Most Viewed Articles (in VB.net )

Latest Articles (in VB.net)

Comment on this tutorial