Solved

Adding Horizontal Page Breaks to Excel Worksheet through VB.NET

Posted on 2011-09-09
4
1,961 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 70

Expert Comment

by:Éric Moreau
ID: 36511455
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
ID: 36511645
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 70

Accepted Solution

by:
Éric Moreau earned 500 total points
ID: 36511939
0
 
LVL 2

Author Closing Comment

by:G_Hosa_Phat
ID: 36512051
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

821 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