Write and Read Excel 2007 and 2010 file - Visual Basic .NET

Tags: VB.NET, VB 2008, VB 2010, VB 2012, VB 2013

Click here for Excel 2000 - 2003

In this tutorial, I will show you how to read and write to Microsoft Excel sheet. Let's say we have a file called temp.xlsx

We want to read cell A1 and return the value to TextBox1, and B1 in TextBox2

excel sheet 

Requirements: a Form, 2 TextBoxes and a Button.

vb.net write and read excel 2007 and excel 2010

Then add "Microsoft Excel 12.0 Object Library" reference. To do that click on Project Menu -- Add Reference, then go to COM tab. Then scroll to Microsoft Excel 12.0 Object Library then click ok.

1.

vb.net project

2.

vb.net add reference

3.

add excel reference

Then go to the code page and add the following declarations:


Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1

Dim APP As New Excel.Application
Dim worksheet As Excel.Worksheet
Dim workbook As Excel.Workbook

Add the following code to Form_Load Event

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _
 Handles MyBase.Load
   workbook = App.Workbooks.Open("Path\temp.xlsx")
   worksheet = workbook.Worksheets("sheet1")
End Sub

To get value from Excel cell to TextBox:

The following code is the Button Click Event that reads the values

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
 Handles Button1.Click
        
        TextBox1.Text = worksheet.Cells(1, 1).Value

        TextBox2.Text = worksheet.Cells(1, 2).Value
End Sub

Output:

read from excel sheet in vb.net

To wirte to excel sheet from a text box:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _ 
Handles Button1.Click
        worksheet.Cells(1, 1).Value = TextBox1.Text
       worksheet.Cells(1, 2).Value = TextBox2.Text
End Sub

Add the following code to the form closing event:
This will save the Excel file and closes it

Private Sub Form1_FormClosed(ByVal sender As System.Object, _
 ByVal e As System.Windows.Forms.FormClosedEventArgs) _
  Handles MyBase.FormClosed
        workbook.Save()        
            workbook.Close()        
            App.Quit()
End Sub


Share This

Home | About | Contact | Privacy Policy

Copyright visual-basic-tutorials.com 2014 - All Rights Reserved.