How to Open and Edit Cells in an Excel 2007 file in VB.NET
By: Issac
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.
Archived Comments
1. There is a .NET Excel API available here:
http://www.aspose.com/.net/excel-component.
View Tutorial By: ishal at 2013-09-23 03:48:50
2. how to refresh the radiobuttons during the run time.
View Tutorial By: Vishnupriya at 2009-02-04 03:25:24
Comment on this tutorial
- Data Science
- Android
- AJAX
- ASP.net
- C
- C++
- C#
- Cocoa
- Cloud Computing
- HTML5
- Java
- Javascript
- JSF
- JSP
- J2ME
- Java Beans
- EJB
- JDBC
- Linux
- Mac OS X
- iPhone
- MySQL
- Office 365
- Perl
- PHP
- Python
- Ruby
- VB.net
- Hibernate
- Struts
- SAP
- Trends
- Tech Reviews
- WebServices
- XML
- Certification
- Interview
categories
Related Tutorials
Using Resume Next and Resume Line in VB.net
Using On Error GoTo 0 in VB.net
Getting an Exception's Number and Description in VB.net
Raising an Exception Intentionally in VB.net
Exception Filtering in the Catch Block in VB.net
Using Multiple Catch Statements in VB.net
Throwing an Exception in VB.net
Throwing a Custom Exception in VB.net
Changes in Controls from VB6 to VB.net
Unstructured Exception Handling in VB.net