Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Adding Horizontal Page Breaks to Excel Worksheet through VB.NET

Posted on 2011-09-09
4
Medium Priority
?
2,259 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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
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…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses

773 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