Friday, 4 January 2013

Open and Edit Cells in an Excel file in VB.NET


The following sections you can find how to open and edit an Excel worksheet through VB.NET . For open or edit an Excel worksheet in VB.NET , you have to add the Microsoft Excel 12.0 Object Library in you project.

From the following pictures to show how to add Excel reference library in your project.

Step1:
Create a new project and add a button to the Form.
Select reference dialouge from Project menu


Step2:
Select Microsoft Excel 12.0 Object Library and click OK button
Step3:
Now you can start coding to open or read from Excel file and edit cells.
Step4:
copy and paste the below code



Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, _
 ByVal e As System.EventArgs) Handles Button1.Click

        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet

        xlApp = New Excel.ApplicationClass
        xlWorkBook = xlApp.Workbooks.Open("c:\test1.xlsx")
        xlWorkSheet = xlWorkBook.Worksheets("sheet1")
        'display the cells value B2
        MsgBox(xlWorkSheet.Cells(2, 2).value)
        'edit the cell with new value
        xlWorkSheet.Cells(2, 2) = "http://vb.net-informations.com"
        xlWorkBook.Close()
        xlApp.Quit()

        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)

    End Sub

    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

End Class