Adding Horizontal Page Breaks to Excel Worksheet through VB.NET
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:
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
Éric Moreau
a long time ago (in 2003), I have asked a question here on Excel PageBreaks. Check it at https://www.experts-exchange.com/questions/20634600/Set-Excel-Page-Break-from-VB-without-using-Microsoft-excel-object-library-9-0.html
ASKER
Thanks for the suggestion, emoreau. I tried this:
.Rows(RowNum).PageBreak = Excel.XlPageBreak.xlPageBreakManual.Rows(RowNum).PageBreak = 1ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.xlPageBr
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.
FYI, I tried it with both of the "generally accepted" methods of adding a page break (.HPageBreaks.Add() and .PageBreak = Excel.XLPageBreak.xlPageBr
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.