troubleshooting Question

Adding Horizontal Page Breaks to Excel Worksheet through VB.NET

Avatar of G_Hosa_Phat
G_Hosa_PhatFlag for United States of America asked on
Visual Basic.NET
4 Comments1 Solution2855 ViewsLast Modified:
Hello again, Experts.

I've been searching EE and Google for a while now, and I'm still not finding a satisfactory solution to what I'm trying to accomplish here.  I'm not sure why this is giving me so much grief, but I'm running out of ideas here.  I have a function that creates an Excel worksheet and populates it with data from a database.  I've made all sorts of "neat" things happen while creating the worksheet to make it look pretty for the end-user, but there's one piece of the puzzle that just seems to elude me - adding a page break.

My application gets two datasets from the database based on stored procedures.  I want there to be a horizontal page break between the results of these two datasets.  I've looked through properties and methods galore, and my searching has turned up a couple of different methods to accomplish this, but none of them seem to actually work.  I don't get an error, so I'm guessing the application thinks that it actually added the page break, but when I open the Excel file and go to the print preview, the page break is obviously not there.

Here's the code I'm using to generate the Excel file:
    Public Function CreateExcelReport() As Boolean
        Dim dtQuery1 As New DataTable
        Dim dtQuery2 As New DataTable

        ' *********************************************************************
        ' ** Get the data from the database that will be used to populate    **
        ' ** the Excel worksheet.                                            **
        ' *********************************************************************
        dtQuery1 = GetQuery1Results()
        dtQuery2 = GetQuery2Results()

        If Not IsNothing(dtQuery1) Or Not IsNothing(dtQuery2) Then
            Dim ExcelApp As Excel.Application
            Dim myBook As Excel.Workbook = Nothing
            Dim mySheet As Excel.Worksheet = Nothing
            Dim SheetCount As Integer = 0
            Dim NumRows As Integer = 0
            Dim SheetName As String = vbNullString
            Dim RowNum As Integer = 0

            ExcelApp = New Excel.Application
            Application.DoEvents()

            Try
                ExcelApp.DisplayAlerts = False

                ' *********************************************************************
                ' ** Create a new workbook.                                          **
                ' *********************************************************************
                myBook = ExcelApp.Workbooks.Add

                ' *********************************************************************
                ' ** Delete any extra sheets created by Excel's default template.    **
                ' *********************************************************************
                If myBook.Sheets.Count > 1 Then
                    For I As Integer = 1 To myBook.Sheets.Count - 1
                        mySheet = New Excel.Worksheet
                        mySheet = CType(myBook.Sheets(I), Excel.Worksheet)
                        mySheet.Delete()

                        mySheet = Nothing
                    Next

                    mySheet = Nothing
                End If

                ' *********************************************************************
                ' ** Activate the sheet for data population.                         **
                ' *********************************************************************
                mySheet = New Excel.Worksheet
                mySheet = CType(myBook.Sheets(1), Excel.Worksheet)
                mySheet.Activate()

                With mySheet
                    ' *********************************************************************
                    ' ** Setup all of the worksheet formatting for viewing and printing. **
                    ' *********************************************************************
                    .Name = "VB.NET Created"
                    .PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape
                    .PageSetup.Zoom = False
                    .PageSetup.FitToPagesWide = 1
                    .PageSetup.TopMargin = 0
                    .PageSetup.BottomMargin = 0
                    .PageSetup.LeftMargin = 0
                    .PageSetup.RightMargin = 0

                    .Range("A1").EntireColumn.ColumnWidth = 20.57
                    .Range("A1").EntireColumn.Font.Name = "Calibri"
                    .Range("A1").EntireColumn.Font.Size = 12

                    .Range("B1").EntireColumn.ColumnWidth = 18.29
                    .Range("B1").EntireColumn.Font.Name = "Calibri"
                    .Range("B1").EntireColumn.Font.Size = 12

                    .Range("C1").EntireColumn.ColumnWidth = 11.29
                    .Range("C1").EntireColumn.Font.Name = "Calibri"
                    .Range("C1").EntireColumn.Font.Size = 12

                    .Range("D1").EntireColumn.ColumnWidth = 53.14
                    .Range("D1").EntireColumn.Font.Name = "Calibri"
                    .Range("D1").EntireColumn.Font.Size = 12

                    .Range("E1").EntireColumn.ColumnWidth = 39
                    .Range("E1").EntireColumn.Font.Name = "Calibri"
                    .Range("E1").EntireColumn.Font.Size = 12

                    .Range("A1").EntireRow.Font.Size = 16
                    .Range("A1").EntireRow.Font.Bold = True
                    .Range("A1:E1").Merge()
                    .Range("A1:E1").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                    .Range("A1:E1").BorderAround()
                    .Range("A1").Cells.Value = "Query 1 Results"

                    ' *********************************************************************
                    ' ** Create the column headings.                                     **
                    ' *********************************************************************
                    .Range("A2:E2").Interior.Color = RGB(192, 192, 192)

                    .Range("A2").Cells.Value = "ID"
                    .Range("A2").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                    .Range("A2").BorderAround()

                    .Range("B2").Cells.Value = "LastName"
                    .Range("B2").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                    .Range("B2").BorderAround()

                    .Range("C2").Cells.Value = "StartDate"
                    .Range("C2").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                    .Range("C2").BorderAround()

                    .Range("D2").Cells.Value = "OWNER NOTES"
                    .Range("D2").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                    .Range("D2").BorderAround()

                    .Range("E2").Cells.Value = "USER NOTES"
                    .Range("E2").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                    .Range("E2").BorderAround()

                    RowNum = 3

                    ' *********************************************************************
                    ' ** Populate the rows for this section from the first dataset.      **
                    ' *********************************************************************
                    If Not IsNothing(dtQuery1) Then
                        For Each ResultRow As DataRow In dtQuery1.Rows
                            .Range("A" & RowNum).Value = ResultRow("ID").ToString
                            .Range("A" & RowNum).BorderAround()

                            .Range("B" & RowNum).Value = ResultRow("LastName").ToString
                            .Range("B" & RowNum).BorderAround()

                            .Range("C" & RowNum).Value = Format(ResultRow("StartDate").ToString, "Short Date")
                            .Range("C" & RowNum).BorderAround()

                            .Range("D" & RowNum).BorderAround()
                            .Range("E" & RowNum).BorderAround()

                            RowNum = RowNum + 1
                        Next
                    Else
                        .Range("A" & RowNum).EntireRow.Font.Size = 16
                        .Range("A" & RowNum).EntireRow.Font.Bold = True
                        .Range("A" & RowNum).EntireRow.Font.Color = RGB(0, 176, 80)
                        .Range("A" & RowNum & ":E" & RowNum).Merge()
                        .Range("A" & RowNum & ":E" & RowNum).BorderAround()
                        .Range("A" & RowNum).Value = "NO RECORDS TO SHOW"

                        RowNum = RowNum + 2
                    End If

                    ' *********************************************************************
                    ' ** Insert a Horizontal Page Break immediately before the heading   **
                    ' ** for the next section (page).  i.e., The next row should be at   **
                    ' ** the top of the next page.
                    ' *********************************************************************
                    .HPageBreaks.Add(.Range("A" & RowNum).EntireRow)
                    ' +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
                    ' ++ I HAVE TRIED SEVERAL INCLUDING SEVERAL VARIATIONS OF THE FOLLOWING SOLUTIONS:
                    ' ++ .HPageBreaks.Add(.Range("A" & RowNum))
                    ' ++ 
                    ' ++ .Range("A" & RowNum).PageBreak = 1
                    ' ++ 
                    ' ++ Dim myRange As Excel.Range = CType(.Cells(RowNum, 1), Excel.Range)
                    ' ++ myRange.PageBreak = 1
                    ' ++ 
                    ' ++ .HPageBreaks.Add(Before:=.Cells(RowNum, 1))
                    ' ++ 
                    ' ++ .Range("A" & RowNum).PageBreak = Excel.XlPageBreak.xlPageBreakManual
                    ' +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

                    ' *********************************************************************
                    ' ** Start the next section for the second dataset.                  **
                    ' *********************************************************************
                    .Range("A" & RowNum).EntireRow.Font.Size = 16
                    .Range("A" & RowNum).EntireRow.Font.Bold = True
                    .Range("A" & RowNum & ":E" & RowNum).Merge()
                    .Range("A" & RowNum & ":E" & RowNum).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                    .Range("A" & RowNum & ":E" & RowNum).BorderAround()
                    .Range("A" & RowNum).Cells.Value = "Query 2 Results"

                    RowNum = RowNum + 1

                    ' *********************************************************************
                    ' ** Create the column headings.                                     **
                    ' *********************************************************************
                    .Range("A" & RowNum & ":E" & RowNum).Interior.Color = RGB(192, 192, 192)

                    .Range("A" & RowNum).Cells.Value = "ID"
                    .Range("A" & RowNum).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                    .Range("A" & RowNum).BorderAround()

                    .Range("B" & RowNum).Cells.Value = "LastName"
                    .Range("B" & RowNum).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                    .Range("B" & RowNum).BorderAround()

                    .Range("C" & RowNum).Cells.Value = "StartDate"
                    .Range("C" & RowNum).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                    .Range("C" & RowNum).BorderAround()

                    .Range("D" & RowNum).Cells.Value = "OWNER NOTES"
                    .Range("D" & RowNum).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                    .Range("D" & RowNum).BorderAround()

                    .Range("E" & RowNum).Cells.Value = "USER NOTES"
                    .Range("E" & RowNum).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                    .Range("E" & RowNum).BorderAround()

                    RowNum = RowNum + 1

                    ' *********************************************************************
                    ' ** Populate the rows for this section from the second dataset.     **
                    ' *********************************************************************
                    If Not IsNothing(dtQuery2) Then
                        For Each ResultRow As DataRow In dtQuery2.Rows
                            .Range("A" & RowNum).Value = ResultRow("ID").ToString
                            .Range("A" & RowNum).BorderAround()

                            .Range("B" & RowNum).Value = ResultRow("LastName").ToString
                            .Range("B" & RowNum).BorderAround()

                            .Range("C" & RowNum).Value = Format(ResultRow("StartDate").ToString, "Short Date")
                            .Range("C" & RowNum).BorderAround()

                            .Range("D" & RowNum).BorderAround()
                            .Range("E" & RowNum).BorderAround()

                            RowNum = RowNum + 1
                        Next
                    Else
                        .Range("A" & RowNum).EntireRow.Font.Size = 16
                        .Range("A" & RowNum).EntireRow.Font.Bold = True
                        .Range("A" & RowNum).EntireRow.Font.Color = RGB(0, 176, 80)
                        .Range("A" & RowNum & ":E" & RowNum).Merge()
                        .Range("A" & RowNum & ":E" & RowNum).BorderAround()
                        .Range("A" & RowNum).Value = "NO RECORDS TO SHOW"
                    End If
                End With

                ' *********************************************************************
                ' ** Save the file in Excel 2000-2003 format for compatability.      **
                ' *********************************************************************
                ExcelApp.Application.ScreenUpdating = True
                ExcelApp.ActiveWorkbook.CheckCompatibility = False
                ExcelApp.ActiveWorkbook.SaveAs("C:\Testing\VB.NET Test.xls", FileFormat:=Excel.XlFileFormat.xlExcel8)
                ExcelApp.ActiveWorkbook.Close(SaveChanges:=True)

                Application.DoEvents()

                Return True
            Catch ex As Exception
                MsgBox(ex.Message)
                 Return False
            Finally
                ' *********************************************************************
                ' ** Clean up the Excel objects to try to prevent orphaned Excel     **
                ' ** processes from staying running in the Task Manager.             **
                ' *********************************************************************
                If Not IsNothing(mySheet) Then
                    ReleaseComObject(mySheet)
                    mySheet = Nothing
                End If

                 If Not IsNothing(myBook) Then
                    ReleaseComObject(myBook)
                    myBook = Nothing
                End If

               If Not IsNothing(ExcelApp) Then
                    ExcelApp.Quit()
                    ReleaseComObject(ExcelApp)
                    ExcelApp = Nothing
                End If

                GC.GetTotalMemory(False)
                GC.Collect()
                GC.WaitForPendingFinalizers()
                GC.Collect()
                GC.GetTotalMemory(True)
           End Try
        End If
    End Function
Like I said, the good thing is that everything else about this function works like a charm.  It's just the insertion of the page break that I can't get to work right.  Anyone have any suggestions or thoughts about why this isn't working, or how I might go about doing it in any other ways?
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros