Deleting Excel worksheets with VB.NET and LINQ

I never claim that the way I do it is the best way…

I created code to insert a worksheet into Excel for each user in the query. But the focus of this post is deleting those default sheets that Excel starts with, Sheet1, Sheet2, and Sheet3. When I tried to delete them at the beginning of my code, I was graciously given this friendly COMException:

A workbook must contain at least one visible worksheet.

No worries, I’ll just do it at the end of the process. I came up with two ways. First, the easy, so un-cool way to do it: Just count backwards from the worksheet count:

Dim bkWorkBook As Excel.Workbook = DirectCast(oXL.Workbooks.Add(), Excel.Workbook)
Dim shWorkSheet As Excel.Worksheet = DirectCast(bkWorkBook.Sheets("Sheet1"), Excel.Worksheet)
'Code to populate the worksheets
Dim Counter As Integer = bkWorkBook.Sheets.Count
CType(bkWorkBook.Sheets(Counter), Excel.Worksheet).Delete()
CType(bkWorkBook.Sheets(Counter - 1), Excel.Worksheet).Delete()
CType(bkWorkBook.Sheets(Counter - 2), Excel.Worksheet).Delete()

Oh, that’s too easy, though. Isn’t there some way I could use some cool LINQ extension method to do this? You betcha. This is the round-about way I came up with using OrderByDescending:

Dim SheetName As String = String.Empty
Dim SheetIds As New List(Of Integer)
For i As Integer = 1 To bkWorkBook.Sheets.Count
            SheetName = CType(bkWorkBook.Sheets(i), Excel.Worksheet).Name
            If SheetName = "Sheet1" OrElse SheetName = "Sheet2" OrElse SheetName = "Sheet3" Then
                SheetIds.Add(i)
            End If
Next
For Each Counter As Integer In SheetIds.OrderByDescending(Function(i As Integer) i)
            CType(bkWorkBook.Sheets(Counter), Excel.Worksheet).Delete()
Next

By the way, I used the first, much simpler, method.

About these ads
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: