Net-informations.com
SiteMap  | About    

Delete worksheet from an excel - VB.Net

Delete excel worksheets programmatically from VB.Net

By default, Excel provides three worksheets in a workbook, but you can add new worksheets or delete worksheets as needed programmatically from VB.Net application. In order to delete a worksheet, you must add a reference to the Microsoft.Office.Interop.Excel assembly, and then you must use classes from that assembly to open a workbook and delete a worksheet.The following VB.Net program shows how to delete worksheet from an an existing Excel file.

Excel Library

To access the object model from Visual VB.Net, you have to add the Microsoft Excel 12.0 Object Library to you project. In the previous chapter you can see a step by step instruction on how to add Excel library to your project.

How to add Excel Library

Delete worksheet from an excel file

How to delete worksheet from an excel file from VB.Net

In order to delete worksheet from excel file, this VB.Net program open an existing Excel file and select the worksheet and then delete it.

Dim worksheets As Excel.Sheets = xlWorkBook.Worksheets worksheets(1).Delete()

Delete Excel.Worksheet without prompts

Delete Excel.Worksheet without prompt from VB.Net

DisplayAlerts set to False for suppress prompts and alert messages while a macro is running. When a message need a response from the user side, Microsoft Excel chooses the default response. After you finish the running process, Microsoft Excel sets this property to True, unless you are running cross-process code.

The following VB.Net source code shows how to programmatically delete Worksheets from Workbooks.




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
        worksheets(1).Delete()
        xlWorkBook.Save()
        xlWorkBook.Close()

        releaseObject(worksheets)
        releaseObject(xlWorkBook)
        releaseObject(xlApp)

        MessageBox.Show("Worksheet Deleted!")
    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




net-informations.com (C) 2016    Founded by raps mk
All Rights Reserved. All other trademarks are property of their respective owners.