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.