My Blog

Page Setup in Excel

In continuation from here

Once you have the data all set in your worksheet, you might want to tweak a few settings in the page setup before the document can be printed. Here is the code:

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    '~~> Define your Excel Objects
    Dim xlApp As New Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        '~~> Opens Source Workbook. Change path and filename as applicable
        xlWorkBook = xlApp.Workbooks.Open("C:\Tutorial\Sample.xlsx")

        '~~> Display Excel
        xlApp.Visible = True

        '~~> Set the source worksheet
        xlWorkSheet = xlWorkBook.Sheets("Sheet1")

        With xlWorkSheet.PageSetup
            .PrintTitleRows = ""
            .PrintTitleColumns = ""
        End With

        With xlWorkSheet.PageSetup
            '~~> In this section, you can define where and what your header 
            '~~> and footer should look like
            .LeftHeader = ""
            .CenterHeader = "Sample Header"
            .RightHeader = ""
            .LeftFooter = ""
            .CenterFooter = "Sample Footer"
            .RightFooter = ""

            '~~> In this section, you can set the margins
            .LeftMargin = xlApp.InchesToPoints(0.45)
            .RightMargin = xlApp.InchesToPoints(0.45)
            .TopMargin = xlApp.InchesToPoints(0.5)
            .BottomMargin = xlApp.InchesToPoints(0.5)
            .HeaderMargin = xlApp.InchesToPoints(0.05)
            .FooterMargin = xlApp.InchesToPoints(0.05)

            '~~> Other settings that you can set
            .PrintHeadings = True
            .PrintGridlines = True
            .PrintComments = Excel.XlPrintLocation.xlPrintNoComments
            .PrintQuality = 1200
            .CenterHorizontally = False
            .CenterVertically = False
            .Orientation = Excel.XlPageOrientation.xlLandscape
            .Draft = True
            .PaperSize = Excel.XlPaperSize.xlPaperA4
            .FirstPageNumber = Excel.Constants.xlAutomatic
            .Order = Excel.XlOrder.xlDownThenOver
            .BlackAndWhite = True
            .PrintErrors = Excel.XlPrintErrors.xlPrintErrorsDisplayed
            .OddAndEvenPagesHeaderFooter = False
            .DifferentFirstPageHeaderFooter = False
            .ScaleWithDocHeaderFooter = True
            .AlignMarginsHeaderFooter = True
        End With

    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
    End Sub
End Class