My Blog

Open excel file and write some text to Excel File

In continuation from here

We created a file earlier here.

Now we will try to open it and insert a record. The code is almost the same.

Insert a button on your form and add 6 textboxes as shown in the image below. We will be accepting inputs and then insert those values into the text file.

This is how our code will look like

Imports System.Data.OleDb

Public Class Form1
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim olecon As OleDbConnection = New OleDbConnection
        Dim olecmd As OleDbCommand = New OleDbCommand
        Dim FilePath As String = "C:\Temp\"
        Dim FileName As String = "EmployeeDatabase.xlsx"

        '~~> Construct your connection string
        Dim connstring As String = "Provider=Microsoft.ACE.OLEDB.12.0;" &
                                   "Data Source=" & FilePath & FileName & ";" &
                                   "Extended Properties=""Excel 12.0 Xml;HDR=YES;"""

        olecon.ConnectionString = connstring
        olecon.Open()

        olecmd.Connection = olecon

        olecmd.CommandText = "Insert into [Sheet1$] " & _
                             "(Sno,Employee_Name,Company,Date_Of_joining,Stipend,Stocks_Held)" & _
                             " values" & _
                             "('" & TextBox1.Text & "','" & _
                             TextBox2.Text & "','" & _
                             TextBox3.Text & "','" & _
                             TextBox4.Text & "','" & _
                             TextBox5.Text & "','" & _
                             TextBox6.Text & "')"

        olecmd.ExecuteNonQuery()

        '~~> Close the connection
        olecon.Close()

        '~~> Inform User
        MessageBox.Show("The record has been entered in the file " & FileName)
    End Sub
End Class

Now you are ready to run your project.

And this is how the inserted record in excel file looks like