My Blog

Adding/Deleting Sheets to the Excel File

In continuation from here

Adding and Deleting sheets in Excel is very simple.

Consider this code. I am also using the DoesSheetExist Function from above in this example to demonstrate the naming of a sheet when you add a new sheet.

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
    Dim NewSheetName As String = "MyNewSheet"

    '~~> Opens an exisiting Workbook. Change path and filename as applicable
    xlWorkBook = xlApp.Workbooks.Open("C:\Tutorial\Sample.xlsx")

    '~~> Display Excel
    xlApp.Visible = True

    '~~> Add a new Sheet
    xlWorkSheet = xlWorkBook.Sheets.Add

    '~~> Check If "MyNewSheet" sheet exists using a function and it doesn't exist then rename the
    '~~> above added sheet to "MyNewSheet"
    If DoesSheetExists(NewSheetName) Then
        MessageBox.Show("The sheet " & NewSheetName & " already exists. Please select another name.")
    Else
        xlWorkSheet.Name = NewSheetName
    End If
End Sub

Public Function DoesSheetExists(ByVal shtName As String) As Boolean
    Dim xs As Excel.Worksheet

    DoesSheetExists = False

    '~~> Loop through the all the sheets in the workbook to find if name matches
    For Each xs In xlWorkBook.Sheets
        If xs.Name = shtName Then
            DoesSheetExists = True
        End If
    Next
End Function

To delete sheet, use this code.

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
    Dim NewSheetName As String = "MyNewSheet"

    '~~> Opens an exisiting Workbook. Change path and filename as applicable
    xlWorkBook = xlApp.Workbooks.Open("C:\Tutorial\Sample.xlsx")

    '~~> Display Excel
    xlApp.Visible = True

    '~~> Add a new Sheet
    xlWorkSheet = xlWorkBook.Sheets.Add

    '~~> Check If "MyNewSheet" sheet exists using a function and it exists then delete it
    If DoesSheetExists(NewSheetName) Then
        xlWorkSheet.Delete()
    Else
        MessageBox.Show("The sheet " & NewSheetName & " Doesn't exists. Please select another name.")
    End If
End Sub

Public Function DoesSheetExists(ByVal shtName As String) As Boolean
    Dim xs As Excel.Worksheet

    DoesSheetExists = False

    '~~> Loop through the all the sheets in the workbook to find if name matches
    For Each xs In xlWorkBook.Sheets
        If xs.Name = shtName Then
            DoesSheetExists = True
        End If
    Next
End Function