Programming Tutorials

How to Open and Edit Cells in an Excel 2007 file in VB.NET

By: Issac in VB.net Tutorials on 2009-01-31  

To open or edit an Excel 2007 worksheet in you have to add Microsoft 12.0 Object Library in your project. From the following pictures to show how to add Excel reference library in your project.

1. Create a new project and add a button to the Form.

Select reference dialouge from Project menu 

Select Microsoft Excel 12.0 Object Library and click OK button 

Now you can start coding to open or read from Excel file and edit cells.

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://java-samples.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

In the code, Imports Excel = Microsoft.Office.Interop.Excel - we assign the excel reference to a variable Excel. 

When you execute this code, the program opens the file in diecory c:\test1.xlsx and edits the content in the cell B2; it replaces the old content to "http://java-samples.com". Before running this program you have to create an excel file name test1.xlsx and add some data in the cell B2.