Add new worksheet in Excel file

Adding new worksheets and managing the active sheet are essential tasks in working with Excel files using VB.Net. The following program demonstrates how to add a new worksheet to an existing Excel file and set the added worksheet as the active sheet using the VB.Net Application.

Excel Library

To utilize the object model in VB.NET and interact with Excel, it is necessary to include the Microsoft Excel 12.0 Object Library in your project's references. In the preceding chapter, you will find a detailed guide outlining the step-by-step process of adding the Excel library to your project, ensuring seamless integration with the Excel object model.

How to add Excel Library

Programmatically Add New Worksheets to Excel

In order to add new worksheet to the excel file, this VB.Net program open an existing Excel file and add a new worksheet in the existing workbook.

Dim worksheets As Excel.Sheets = xlWorkBook.Worksheets Dim xlNewSheet = DirectCast(worksheets.Add(worksheets(1), Type.Missing, Type.Missing, Type.Missing), Excel.Worksheet) xlNewSheet.Name = "newsheet" xlNewSheet.Cells(1, 1) = "New sheet content"

Disable Excel overwrite promt - VB.Net

xlApp.DisplayAlerts = False

By setting the DisplayAlerts property to False in Excel, prompts and alert messages are suppressed during the execution of a macro. This means that when a message requires a response from the user, Excel automatically selects the default response without interrupting the macro. It's worth noting that after the execution of the macro is completed, Excel reverts the DisplayAlerts property to True, unless the code being run is cross-process in nature. This ensures that normal alert behavior resumes once the macro has finished running.

Programmatically Select Worksheets - VB.Net

You can Programmatically select Worksheet and set focus on that worksheet when user open the Excel file.

xlNewSheet = xlWorkBook.Sheets("sheet1") xlNewSheet.Select()

Above method shows how to select (focus) a specified worksheet, in this way you can select any existing worksheet from an Excel document.

releaseObject()

It is important to perform proper cleanup and release of Excel interop objects after your program has completed its execution. This ensures that any resources held by the Excel COM objects are properly released and avoids potential memory leaks. To achieve this, a function called releaseObject() can be used to clean up the Excel object effectively.

The releaseObject() function should be called for each Excel object that is instantiated in your code and is no longer needed. It helps in releasing the associated resources and allows for efficient memory management. By calling this function, you ensure that any references to the Excel objects are properly released and can be garbage collected by the .NET runtime.

Properly cleaning up Excel interop objects is good programming practice and helps maintain the stability and performance of your application. It is recommended to include the releaseObject() function in your code to handle the cleanup process systematically and ensure the proper release of resources associated with Excel COM objects.

The following VB.Net source code shows how to insert new worksheet in an excel file.

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 = New Microsoft.Office.Interop.Excel.Application() If xlApp Is Nothing Then MessageBox.Show("Excel is not properly installed!!") Return End If xlApp.DisplayAlerts = False Dim filePath As String = "d:\test.xlsx" Dim xlWorkBook As Excel.Workbook = xlApp.Workbooks.Open(filePath, 0, False, 5, "", "", _ False, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", True, False, 0, _ True, False, False) Dim worksheets As Excel.Sheets = xlWorkBook.Worksheets Dim xlNewSheet = DirectCast(worksheets.Add(worksheets(1), Type.Missing, Type.Missing, Type.Missing), Excel.Worksheet) xlNewSheet.Name = "newsheet" xlNewSheet.Cells(1, 1) = "New sheet content" xlNewSheet = xlWorkBook.Sheets("sheet1") xlNewSheet.Select() xlWorkBook.Save() xlWorkBook.Close() releaseObject(xlNewSheet) releaseObject(worksheets) releaseObject(xlWorkBook) releaseObject(xlApp) MessageBox.Show("New Worksheet Created!") 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





Related Topics
  1. How to create an Excel file in VB.NET
  2. How to open or read an existing Excel file in VB.NET
  3. How to read an Excel file using 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