Solved

Adding Horizontal Page Breaks to Excel Worksheet through VB.NET

Posted on 2011-09-09
4
1,833 Views
Last Modified: 2012-05-12
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

Open in new window

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?
0
Comment
Question by:G_Hosa_Phat
  • 2
  • 2
4 Comments
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
a long time ago (in 2003), I have asked a question here on Excel PageBreaks. Check it at http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_20634600.html
0
 
LVL 2

Author Comment

by:G_Hosa_Phat
Comment Utility
Thanks for the suggestion, emoreau.  I tried this:
.Rows(RowNum).PageBreak = Excel.XlPageBreak.xlPageBreakManual

Open in new window

and this:
.Rows(RowNum).PageBreak = 1

Open in new window

I first had to turn off Option Strict so I didn't have to worry about late binding errors, but it still didn't work.  There is still no page break between the sections.  Of course, it's always possible I'm just missing something here...
0
 
LVL 69

Accepted Solution

by:
Éric Moreau earned 500 total points
Comment Utility
0
 
LVL 2

Author Closing Comment

by:G_Hosa_Phat
Comment Utility
Okay, that worked.  I had to do a little reformatting of the columns to get everything to fit to one page wide properly now, but it correctly added the page break finally.

FYI, I tried it with both of the "generally accepted" methods of adding a page break (.HPageBreaks.Add() and .PageBreak = Excel.XLPageBreak.xlPageBreakManual), just to see the results, and both of them worked exactly the same.  Just in case anyone was wondering...

Interestingly enough, as I was playing around with it, I found that it was actually okay to have the FitToPagesWide property set as long as the Zoom property (right above it) was not set (of course, the FitToPagesWide property doesn't DO anything if the Zoom property isn't set, so it's a moot point).

Very strange and disappointing.  It makes a little sense that the two might conflict, but not really considering the fact that you can manually create a workbook with all of those settings and it works just fine.

Thanks for the pointer, emoreau.  Now I can move on to my next excercise in banging my head against a wall.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now