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

To open and edit an Excel worksheet through VB.NET, you need to add the Microsoft Excel 12.0 Object Library to your project. This library provides the necessary components and functionalities to interact with Excel.

Here are the steps to add the Microsoft Excel 12.0 Object Library to your VB.NET project:

  1. Open your VB.NET project in Visual Studio.
  2. Go to the "Solution Explorer" window.
  3. Right-click on the "References" node and select "Add Reference."
  4. In the "Reference Manager" window, navigate to the "COM" tab.
  5. Scroll down and locate "Microsoft Excel 12.0 Object Library" or a similar version, depending on the Excel version installed on your system.
  6. Check the checkbox next to the library and click "OK" to add the reference to your project.
  7. Once you have added the reference, you can start working with Excel in your VB.NET code.

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
excel_reference.jpg
Select Microsoft Excel 12.0 Object Library and click OK button

excel_library.jpg

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

Full Source VB.NET
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) = "https://net-informations.com/vb/default.htm" 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 vatriable Excel.

When you execute this program , the program open the file c:\test1.xlsx and edit the content in the cell B2, it replace the old content to "https://net-informations.com/vb/default.htm" . Before running this program you have to create an excel file name test1.xlsx and add some data in the cell B2.






Related Topics
  1. How to create an Excel file in VB.NET
  2. How to read an Excel file using VB.Net
  3. Add new worksheet in Excel file - VB.Net
  4. Delete worksheet from an excel file - VB.Net
  1. How to Format Excel Page in VB.NET
  2. How to insert a Picture in Excel through programing on VB.NET
  3. How to insert a background Picture in Excel through VB.NET
  4. How to create a Chart in Excel in VB.NET
  5. How to export a Chart in Excel as Picture file from VB.NET
  6. How to Excel Chart in VB.NET Picture Box
  7. How to Excel DataBar in VB.NET
  8. How to Excel Data Validation Input Message
  9. Read and Import Excel File into DataSet or DataTable in vb.net
  10. How to insert cell data in an Excel file using OLEDB
  11. How to update cell data in an Excel file using OLEDB
  12. How to export from database to excel
  13. How to export from DataGridView to excel in VB.Net