Update cell data in an Excel file using OLEDB

In VB.NET, developers have the ability to manipulate cell content within an Excel file without relying on the Excel Object. This can be accomplished through the utilization of OLEDB (Object Linking and Embedding Database) technology, which enables direct interaction with Excel files.

To facilitate these operations, specific components such as OleDbConnection, OleDbDataAdapter, and DataSet come into play. These components play crucial roles in establishing connections to Excel files, executing commands, and managing data operations within the file. Importing the System.Data namespace is imperative as it grants access to these components and their functionalities.

Sample UPDATE sql:
sql = "Update [Sheet1$] set name = 'New Name' where id=1"

The following picture shows before and after update of the Sheet.

vb.net_excel_update_oledb.JPG

When it comes to updating or modifying cell content, developers can utilize the UPDATE command, reminiscent of its usage in SQL operations. This command empowers developers to specify the target cell or range and supply the updated content to be applied. By utilizing the power of the UPDATE command, developers can efficiently modify existing data within Excel cells, ensuring accuracy and up-to-date information.

Full Source VB.NET
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

Conclusion

It is important to note that utilizing OLEDB and its related components necessitates importing the required namespaces and ensuring proper project configuration. With these preparations in place, developers can effectively update cell content in Excel files, thereby providing a robust solution for managing and modifying data within the spreadsheet.






Related Topics
  1. How to create an Excel file in VB.NET
  2. How to open or read an existing Excel file in VB.NET
  3. How to read an Excel file using VB.Net
  4. Add new worksheet in Excel file - VB.Net
  1. Delete worksheet from an excel file - VB.Net
  2. How to Format Excel Page in VB.NET
  3. How to insert a Picture in Excel through programing on VB.NET
  4. How to insert a background Picture in Excel through VB.NET
  5. How to create a Chart in Excel in VB.NET
  6. How to export a Chart in Excel as Picture file from VB.NET
  7. How to Excel Chart in VB.NET Picture Box
  8. How to Excel DataBar in VB.NET
  9. How to Excel Data Validation Input Message
  10. Read and Import Excel File into DataSet or DataTable in vb.net
  11. How to insert cell data in an Excel file using OLEDB
  12. How to export from database to excel
  13. How to export from DataGridView to excel in VB.Net