SoftArtisans ExcelWriter Guide

Show code in...

The PageSetup Object (SAPageSetup)

* The PageSetup object is not available in ExcelWriterSE, ExcelWriterLE, or ExcelWriterFree.

The PageSetup object represents the page layout properties of a printed worksheet. To create a PageSetup object, use,

Set PageSetup = Worksheet.PageSetup

Note: Since this object is tightly related to client's printer setup, for those computers without a printer setup, changes to this object have no effect.

PageSetup Methods and Properties

*This feature is not available in ExcelWriterSE, ExcelWriterLE, or ExcelWriterFree.

PageSetup Methods and Properties

BlackAndWhite*

Set the BlackAndWhite property to true if you formatted data with colors, but are printing on a black and white printer. If you are using a color printer, setting BlackAndWhite to true may reduce printing time. BlackAndWhite is set to False by default.

Signature:


[VBScript]
Property BlackAndWhite As Boolean (read/write)

Example:



[VBScript]

'--- Create an instance of ExcelWriter and get a reference
'--- to the first worksheet in the workbook
Dim XlwApp, WrkSht
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set WrkSht = XlwApp.Worksheets(1)

'--- Using the worksheet's SAPageSetup object, set
'--- BlackAndWhite to true
WrkSht.PageSetup.BlackAndWhite = True

BottomMargin*

Set BottomMargin to specify distance in inches between the worksheet data and the bottom edge of the printed page. BottomMargin is set to 0 by default.

Signature:


[VBScript]
Property BottomMargin As Double (read/write)

Example:



[VBScript]

'--- Create an instance of ExcelWriter and get a reference
'--- to the first worksheet in the workbook
Dim XlwApp, WrkSht
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set WrkSht = XlwApp.Worksheets(1)

'--- Set the Bottom Margin to 1.25 inches from the worksheet data
WrkSht.PageSetup.BottomMargin = 1.25

CenterFooter*

Sets or returns a center footer that will appear on the printed worksheet. For instructions on formatting headers and footers see, Formatting Headers and Footers.

Signature:


[VBScript]
Property CenterFooter As String (read/write)

Example:



[VBScript]

'--- Create an instance of ExcelWriter and get a reference
'--- to the first worksheet in the workbook
Dim XlwApp, WrkSht
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set WrkSht = XlwApp.Worksheets(1)

'--- Using the PageSetup object, set the center footer to "Page X or Y"
WrkSht.PageSetup.CenterFooter = "Page &P of &N"

CenterHeader*

Sets or returns a center header that will appear on the printed worksheet. For instructions on formatting headers and footers see, Formatting Headers and Footers.

Signature:


[VBScript]
Property CenterHeader As String (read/write)

Example:



[VBScript]

'--- Create an instance of ExcelWriter and get a reference
'--- to the first worksheet in the workbook
Dim XlwApp, WrkSht
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set WrkSht = XlwApp.Worksheets(1)

'--- Using the PageSetup object, set the center header
WrkSht.PageSetup.CenterHeader = "Your Worksheet's Center Header"

CenterHorizontally*

When set to True, centers data horizontally within the margins. CenterHorizontally is set to False by default.

Signature:


[VBScript]
Property CenterHorizontally As Boolean (read/write)

Example:



[VBScript]

'--- Create an instance of ExcelWriter and get a reference
'--- to the first worksheet in the workbook
Dim XlwApp, WrkSht
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set WrkSht = XlwApp.Worksheets(1)

'--- Set "CenterHorizontally" to "true" to center data within the margins
WrkSht.PageSetup.CenterHorizontally = True

CenterVertically*

When set to True, centers data vertically within the margins. CenterVertically is set to False by default.

Signature:


[VBScript]
Property CenterVertically As Boolean (read/write)

Example:



[VBScript]

'--- Create an instance of ExcelWriter and get a reference
'--- to the first worksheet in the workbook
Dim XlwApp, WrkSht
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set WrkSht = XlwApp.Worksheets(1)

'--- Set "CenterVertically" to "true" to center data vertically within the margins
WrkSht.PageSetup.CenterVertically = True

Draft*

Set Draft to true to reduce printing time. When Draft is set to true, Excel does not print gridlines and most graphics. Draft is set to False by default.

Signature:


[VBScript]
Property Draft As Boolean

Example:



[VBScript]

'--- Create an instance of ExcelWriter and get a reference
'--- to the first worksheet in the workbook
Dim XlwApp, WrkSht
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set WrkSht = XlwApp.Worksheets(1)

'--- Enable draft printing by setting Draft to True
WrkSht.PageSetup.Draft = True

FirstPageNumber*

Sets or returns the first page number for the printed worksheet. By default, the first page will be 1, or, if the print job does not start with page 1, FirstPageNumber will be the number of the first page in the sequence of pages to print.

Signature:


[VBScript]
Property FirstPageNumber As Integer (read/write)

Example:



[VBScript]

'--- Create an instance of ExcelWriter and get a reference
'--- to the first worksheet in the workbook
Dim XlwApp, WrkSht
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set WrkSht = XlwApp.Worksheets(1)

'--- Begin page numbering on a number other than 1
WrkSht.PageSetup.FirstPageNumber = 3

FitToPagesTall*

Adjusts the height of the worksheet or selection from worksheet to fit into a specified number of pages. Use in conjunction with FitToPagesWide. FitToPagesTall is set to 1 by default.

Note: The pair of properties FitToPagesWide and FitToPagesTall and Zoom are mutually exclusive.

Signature:


[VBScript]
Property FitToPagesTall As Variant (read/write)

Example:



[VBScript]

'--- Create an instance of ExcelWriter and get a reference
'--- to the first worksheet in the workbook
Dim XlwApp, WrkSht
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set WrkSht = XlwApp.Worksheets(1)

'--- When printing, fit all content on two vertical pages 
WrkSht.PageSetup.FitToPagesTall = 2

FitToPagesWide*

Adjusts the width of the worksheet or selection from worksheet to fit into a specified number of pages. Use in conjunction with FitToPagesTall. FitToPagesWide is set to 1 by default.

Note: The pair of properties FitToPagesWide and FitToPagesTall and Zoom are mutually exclusive.

Signature:


[VBScript]
Property FitToPagesWide As Variant (read/write)

Example:



[VBScript]

'--- Create an instance of ExcelWriter and get a reference
'--- to the first worksheet in the workbook
Dim XlwApp, WrkSht
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set WrkSht = XlwApp.Worksheets(1)

'--- When printing, fit all content on two horizontal pages 
WrkSht.PageSetup.FitToPagesWide = 2

FooterMargin*

User FooterMargin to adjust the distance in inches between the footer and the top of the page. The FooterMargin should be smaller than the BottomMargin to prevent the footer from overlapping the data. FooterMargin is set to .5 by default.

Signature:


[VBScript]
Property FooterMargin As Double (read/write)

Example:

		ws.PageSetup.FooterMargin= .5

GetPrintTitles*

Returns a Range object that contains titles that will be repeated on each page of a printed worksheet.

Signature:


[VBScript]
Function GetPrintTitles() As SARange

The range returned by GetPrintTitles includes,

The area set by SetPrintTitleColumns, if SetPrintTitleRows is not set

OR

The area set by SetPrintTitleRows, if SetPrintTitleColumns is not set

OR

A union of the two areas set by SetPrintTitleColumns and SetPrintTitleRows, if both are set

Example:



[VBScript]

'--- Create an instance of ExcelWriter and get a reference
'--- to the first worksheet in the workbook
Dim XlwApp, WrkSht, TitleRange
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set WrkSht = XlwApp.Worksheets(1)

'--- Get a SARange object that contains the Print Title Rows and Columns
'--- for the worksheet
Set TitleRange = WrkSht.PageSetup.GetPrintTitles()

HeaderMargin*

Use HeaderMargin to adjust the distance between the header and the top of the page. The HeaderMargin should be smaller than the TopMargin to prevent the header from overlapping the data. HeaderMargin is set to .5 by default.

Signature:


[VBScript]
Property HeaderMargin As Double (read/write)

Example:



[VBScript]

'--- Create an instance of ExcelWriter and get a reference
'--- to the first worksheet in the workbook
Dim XlwApp, WrkSht
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set WrkSht = XlwApp.Worksheets(1)

'--- Set the worksheet's header margin to 1.5 inches
WrkSht.PageSetup.HeaderMargin = 1.5

LeftFooter*

Sets or returns a left footer that will appear on the printed worksheet. For instructions on formatting headers and footers see, Formatting Headers and Footers.

Signature:


[VBScript]
Property LeftFooter As String (read/write)

Example:



[VBScript]

'--- Create an instance of ExcelWriter and get a reference
'--- to the first worksheet in the workbook
Dim XlwApp, WrkSht
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set WrkSht = XlwApp.Worksheets(1)

'--- Set the left footer for the worksheet
WrkSht.PageSetup.LeftFooter = "The current date is &D"

LeftHeader*

Sets or returns a left header that will appear on the printed worksheet. For instructions on formatting headers and footers see, Formatting Headers and Footers.

Signature:


[VBScript]
Property LeftHeader As String (read/write)

Example:



[VBScript]

'--- Create an instance of ExcelWriter and get a reference
'--- to the first worksheet in the workbook
Dim XlwApp, WrkSht
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set WrkSht = XlwApp.Worksheets(1)

'--- Set the left header for the worksheet
WrkSht.PageSetup.LeftHeader = "The current time is &T"

LeftMargin*

Set LeftMargin to specify distance between the worksheet data and the left edge of the printed page. LeftMargin is set to 0 by default.

Signature:


[VBScript]
Property LeftMargin As Double (read/write)

Example:



[VBScript]

'--- Create an instance of ExcelWriter and get a reference
'--- to the first worksheet in the workbook
Dim XlwApp, WrkSht
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set WrkSht = XlwApp.Worksheets(1)

'--- Set the left margin to 0.5 inches
WrkSht.PageSetup.LeftMargin = 0.5

Order*

Set the Order property to control the order in which data is numbered and printed when it does not fit on one page.

Signature:


[VBScript]
Property Order As SAXlOrder (read/write)

The Order value names and codes are,

saxlDownThenOver	1
saxlOverThenDown	2

The figure below previews the direction that the document will print when you set one of these values. Order is set to saxlDownThenOver by default.

Example:



[VBScript]

'--- Create an instance of ExcelWriter and get a reference
'--- to the first worksheet in the workbook
Dim XlwApp, WrkSht
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set WrkSht = XlwApp.Worksheets(1)

'--- Set the page print order to "over then down"
WrkSht.PageSetup.Order = saxlOverThenDown

Orientation*

Sets or returns the orientation of the printed worksheet.

Signature:


[VBScript]
Property Orientation As SAXlPageOrientation (read/write)

Assign an Orientation value by its name or code. The Orientation value names and codes are,

saxlPortrait	1
saxlLandscape	2

Orientation is set to saxlPortrait by default. The following sets a worksheet's orientation to landscape.

Example:



[VBScript]

'--- Create an instance of ExcelWriter and get a reference
'--- to the first worksheet in the workbook
Dim XlwApp, WrkSht
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set WrkSht = XlwApp.Worksheets(1)

'--- Set the page orientation to Landscape
WrkSht.PageSetup.Orientation = saxlLandscape

PaperSize*

Sets or returns the paper size for the printed worksheet.

Signature:


[VBScript]
Property PaperSize As SAXlPaperSize (read/write)

Assign PaperSize by name or number, according to the following table.

saxlPaperLetter	1	saxlPaperEnvelope12	22
saxlPaperLetterSmall	2	saxlPaperEnvelope14	23
saxlPaperTabloid	3	saxlPaperCsheet	24
saxlPaperLedger	4	saxlPaperDsheet	25
saxlPaperLegal	5	saxlPaperEsheet	26
saxlPaperStatement	6	saxlPaperEnvelopeDL	27
saxlPaperExecutive	7	saxlPaperEnvelopeC5	28
saxlPaperA3	8	saxlPaperEnvelopeC3	29
saxlPaperA4	9	saxlPaperEnvelopeC4	30
saxlPaperA4Small	10	saxlPaperEnvelopeC6	31
saxlPaperA5	11	saxlPaperEnvelopeC65	32
saxlPaperB4	12	saxlPaperEnvelopeB4	33
saxlPaperB5	13	saxlPaperEnvelopeB5	34
saxlPaperFolio	14	saxlPaperEnvelopeB6	35
saxlPaperQuarto	15	saxlPaperEnvelopeItaly	36
saxlPaper10x14	16	saxlPaperEnvelopeMonarch	37
saxlPaper11x17	17	saxlPaperEnvelopePersonal	38
saxlPaperNote	18	saxlPaperFanfoldUS	39
saxlPaperEnvelope9	19	saxlPaperFanfoldStdGerman	40
saxlPaperEnvelope10	20	saxlPaperFanfoldLegalGerman	41
saxlPaperEnvelope11	21	saxlPaperUser	256

PaperSize is set to saxlPaperLetter by default.

Example:



[VBScript]

'--- Create an instance of ExcelWriter and get a reference
'--- to the first worksheet in the workbook
Dim XlwApp, WrkSht
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set WrkSht = XlwApp.Worksheets(1)

'--- Set the paper size to A4
WrkSht.PageSetup.PaperSize = saxlPaperA4

PrintArea*

Sets or returns, as a Range object, a worksheet's print area.

Signature:


[VBScript]
Property PrintArea As SARange (read/write)

The following example sets a worksheet's print area to a range that begins at cell A1 and spans 20 rows and 10 columns.

Example:



[VBScript]

'--- Create an instance of ExcelWriter and get a reference
'--- to the first worksheet in the workbook
Dim XlwApp, WrkSht, PrintRange
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set WrkSht = XlwApp.Worksheets(1)

'--- Create the range A1:J20
Set PrintRange = WrkSht.Cells.Range(1,1,20,10)

'--- Assign the range to the PrintArea property
WrkSht.PageSetup.PrintArea = PrintRange

PrintComments*

Use PrintComments to set your preference on printing the notes that may be attached to cells.

Signature:


[VBScript]
Property PrintComments As SAXlPrintLocation (read/write)

Select saxlPrintSheetEnd to print comments beginning an a separate page at the end of the document. Select PrintInPlace to print comments where they are displayed when you view the worksheet. Please note, if you use PrintInPlace, Excel will only print the comments that are displayed. PrintComments is used when you want precise control of what and where comments are printed. PrintNotes simply prints comments in place or does not print comments. Do not use PrintComments and PrintNotes together.

The PrintComments value names and codes are,

saxlPrintInPlace	-16
saxlPrintNoComments	0
saxlPrintSheetEnd	-1

PrintComments is set to saxlPrintNoComments by default.

Example:



[VBScript]

'--- Create an instance of ExcelWriter and get a reference
'--- to the first worksheet in the workbook
Dim XlwApp, WrkSht
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set WrkSht = XlwApp.Worksheets(1)

'--- Use the PrintComment property to set the location
'--- of the comments when printing
'--- saxlPrintInPlace prints the comments in place
WrkSht.PageSetup.PrintComments = saxlPrintInPlace

PrintGridlines*

Set PrintGridlines to print horizontal and vertical cell gridlines on worksheets. PrintGridlines is set to False by default.

Signature:


[VBScript]
Property PrintGridLines As Boolean (read/write)

Example:



[VBScript]

'--- Create an instance of ExcelWriter and get a reference
'--- to the first worksheet in the workbook
Dim XlwApp, WrkSht
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set WrkSht = XlwApp.Worksheets(1)

'--- Enable printing of sheet grid lines
WrkSht.PageSetup.PrintGridLines = True

PrintHeadings*

Set PrintHeadings to print row numbers and column letters in the A1 reference style or numbered rows and columns in the R1C1 reference style. PrintHeadings is set to False by default.

Signature:


[VBScript]
Property PrintHeadings As Boolean (read/write)

Example:



[VBScript]

'--- Create an instance of ExcelWriter and get a reference
'--- to the first worksheet in the workbook
Dim XlwApp, WrkSht
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set WrkSht = XlwApp.Worksheets(1)

'--- Enable printing of row and column headings (eg, A1, R1C1)
WrkSht.PageSetup.PrintHeadings = True

PrintNotes*

Use PrintNotes to print notes that may be attached to cells. Set PrintNotes to true to print notes where they are displayed when you view the worksheet. (For more precise control of where notes are printed, please see PrintComments.) PrintNotes is set to False by default. Do not use PrintNotes and PrintComments together.

Signature:


[VBScript]
Property PrintNotes As Boolean (read/write)

Example:



[VBScript]

'--- Create an instance of ExcelWriter and get a reference
'--- to the first worksheet in the workbook
Dim XlwApp, WrkSht
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set WrkSht = XlwApp.Worksheets(1)

'--- Set PrintNotes = True to print any notes associated with cells 
WrkSht.PageSetup.PrintNotes = True

RightFooter*

Sets or returns a right footer that will appear on the printed worksheet.

Signature:


[VBScript]
Property RightFooter As String (read/write)

For instructions on formatting headers and footers see, Formatting Headers and Footers.

Example:



[VBScript]

'--- Create an instance of ExcelWriter and get a reference
'--- to the first worksheet in the workbook
Dim XlwApp, WrkSht
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set WrkSht = XlwApp.Worksheets(1)

'--- Set the text of the right footer for the worksheet
WrkSht.PageSetup.RightFooter = "This document was printed on: &D"

RightHeader*

Sets or returns a right header that will appear on the printed worksheet.

Signature:


[VBScript]
Property RightHeader As String (read/write)

For instructions on formatting headers and footers see, Formatting Headers and Footers.

Example:



[VBScript]

'--- Create an instance of ExcelWriter and get a reference
'--- to the first worksheet in the workbook
Dim XlwApp, WrkSht
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set WrkSht = XlwApp.Worksheets(1)

'--- Set the text of the right header for the worksheet
WrkSht.PageSetup.RightHeader = "This document was printed at time: &T"

RightMargin*

Set RightMargin to specify distance in inches between the worksheet data and the right edge of the printed page. RightMargin is set to 0 by default.

Signature:


[VBScript]
Property RightMargin As Double (read/write)

Example:



[VBScript]

'--- Create an instance of ExcelWriter and get a reference
'--- to the first worksheet in the workbook
Dim XlwApp, WrkSht
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set WrkSht = XlwApp.Worksheets(1)

'--- Set the right margin to 0.5 inches
WrkSht.PageSetup.RightMargin = 0.5

SetPrintTitleColumns*

Use SetPrintTitleColumns to set title columns that will be repeated on each page of a printed worksheet.

Signature:


[VBScript]
Sub SetPrintTitleColumns([FirstColumn As Long = 1], [NumColumns As Long = 1])

In VBScript, SetPrintTitleColumns takes two optional parameters:

FirstColumn Default Value: 1

NumColumns Default Value: 1

The following example sets a title column at column B.

Example:



[VBScript]

'--- Create an instance of ExcelWriter and get a reference
'--- to the first worksheet in the workbook
Dim XlwApp, WrkSht
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set WrkSht = XlwApp.Worksheets(1)

'--- Set the print title column at column B
WrkSht.PageSetup.SetPrintTitleColumns 2,1

See also, SetPrintTitleRows and GetPrintTitles.

SetPrintTitleRows*

Use SetPrintTitleRows to set title rows that will be repeated on each page of a printed worksheet.

Signature:


[VBScript]
Sub SetPrintTitleRows([FirstRow As Long = 1], [NumRow As Long = 1])

In VBScript, SetPrintTitleRows takes two optional parameters:

FirstRow Default Value: 1

NumRows Default Value: 1

The following sets a title row at row 3.

Example:



[VBScript]

'--- Create an instance of ExcelWriter and get a reference
'--- to the first worksheet in the workbook
Dim XlwApp, WrkSht
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set WrkSht = XlwApp.Worksheets(1)

'--- Set the print title row at row 3
WrkSht.PageSetup.SetPrintTitleRows 3,1

See also, SetPrintTitleColumns and GetPrintTitles.

TopMargin*

Set TopMargin to specify distance in inches between the worksheet data and the top edge of the printed page. TopMargin is set to 0 by default.

Signature:


[VBScript]
Property TopMargin As Double (read/write)

Example:



[VBScript]

'--- Create an instance of ExcelWriter and get a reference
'--- to the first worksheet in the workbook
Dim XlwApp, WrkSht
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set WrkSht = XlwApp.Worksheets(1)

'--- Set the top margin to 1.5 inches
WrkSht.PageSetup.TopMargin = 1.5

UseZoom*

Returns True if Zoom is enabled, and False if Zoom is disabled. If Zoom is disabled, FitToPagesWide and FitToPagesTall are enabled.

Signature:


[VBScript]
Property UseZoom As Boolean (read-only)

Example:



[VBScript]

'--- Create an instance of ExcelWriter and get a reference
'--- to the first worksheet in the workbook
Dim XlwApp, WrkSht
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set WrkSht = XlwApp.Worksheets(1)

'--- Test the UseZoom property to see 
'--- what combinatino of FitToPagesWide/Tall and Zoom 
'--- should be used
If WrkSht.PageSetup.UseZoom Then
	WrkSht.PageSetup.Zoom = 150
Else
	WrkSht.PageSetup.FitToPagesWide = 2
	WrkSht.PageSetup.FitToPagesTall = 3
End If

Zoom*

Adjusts the size of the printed worksheet by a specified percentage. The printed worksheet can be enlarged up to 400%, and reduced to 10%. To enable Zoom, set it to True, and assign a percentage value to it. Zoom is set to 100 by default.

Signature:


[VBScript]
Property Zoom As Variant (read/write)

Note: Zoom and the pair of properties FitToPagesWide and FitToPagesTall are mutually exclusive.

Example: The following enlarges the printed worksheet to 150% of its normal size.

Example:



[VBScript]

'--- Create an instance of ExcelWriter and get a reference
'--- to the first worksheet in the workbook
Dim XlwApp, WrkSht
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set WrkSht = XlwApp.Worksheets(1)

'--- Set Zoom to 150% of normal size
WrkSht.PageSetup.Zoom = 150

See also, UseZoom.

*This feature is not available in ExcelWriterSE, ExcelWriterLE, or ExcelWriterFree.

Copyright © 2005, SoftArtisans, Inc.