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 . In order to add new content in the cell, we can use the INSERT command, known to any SQL users.
Sample Insert SQL
sql = "Insert into [Sheet1$] (id,name) values('5','e')"
The following code creates an OleDbConnection to the excel file and adds in a few new rows.
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 = "Insert into [Sheet1$] (id,name) values('5','e'); INSERT into [Sheet1$]) (id, name) VALUES ('50','fifty is a very good number!');" myCommand.CommandText = sql myCommand.ExecuteNonQuery() MyConnection.Close() Catch ex As Exception MsgBox(ex.ToString) End Try MsgBox("Row Added ") End Sub End Class
When you execute this source code the will insert a row in the Excel file.