Solved

Adding Horizontal Page Breaks to Excel Worksheet through VB.NET

Posted on 2011-09-09
4
2,113 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
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…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

623 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