Formatting Headers and Footers
Formatting Headers and Footers with ExcelWriter
ExcelWriter allows you to create and format headers and footers for your spreadsheet in
script using the the PageSetup object properties
LeftFooter,
CenterFooter,
RightFooter,
LeftHeader,
CenterHeader, and
RightHeader.
To format a header or footer in script, include formatting codes within the ExcelWriter
header/footer property. For example, the following displays the center header text in bold Tahoma:
ws.PageSetUp.CenterHeader = "&""Tahoma""&BFormatting Headers and Footers&B"
The following example demonstrates formatting headers and footers in an ExcelWriter script.
<%@ Language=VBScript %>
<% Option Explicit %>
<!-- METADATA TYPE="TypeLib" UUID="{7BCD2133-64A0-4770-843C-090637114583}" -->
<%
Dim oXLW
Dim oWS
Set oXLW = Server.CreateObject("Softartisans.ExcelWriter")
Set oWS = oXLW.Worksheets(1)
oWS.Cells(2,1).Value = "To see the headers and footers, open the File menu, "
oWS.Cells(3,1).Value = "select Page Setup, and select Header/Footer."
'---Format the headers and footers using VBA format codes
oWS.PageSetUp.LeftHeader = "&""Tahoma, Bold""&16&ESoftArtisans&E"
oWS.PageSetUp.CenterHeader = "&""Tahoma""&IFormatting Headers/Footers&I"
oWS.PageSetUp.RightHeader = "&""Tahoma""&A"
oWS.PageSetUp.LeftFooter = "&""Tahoma""&D"
oWS.PageSetUp.CenterFooter = "&""Tahoma""&T"
oWS.PageSetUp.RightFooter = "&""Tahoma""Page &P"
oXLW.Save "out.xls", saOpenInPlace
Set oXLW = Nothing
%>
| Formatting Codes for Headers and Footers |
|---|
| &L | Left aligns the characters that follow. |
|---|
| &C | Centers the characters that follow. |
|---|
| &R | Right aligns the characters that follow. |
|---|
| &E | Turns double-underline on or off. |
|---|
| &X | Turns superscript on or off. |
|---|
| &Y | Turns subscript on or off. |
|---|
| &B | Turns bold on or off. |
|---|
| &I | Turns italic on or off. |
|---|
| &U | Turns underline on or off. |
|---|
| &S | Turns strikethrough on or off. |
|---|
| &D | Prints the current date. |
|---|
| &T | Prints the current time. |
|---|
| &F | Prints the name of the document. |
|---|
| &A | Prints the name of the worksheet. |
|---|
| &P | Prints the page number. |
|---|
| &P+number | Prints the page number plus the specified number. |
|---|
| &P-number | Prints the page number minus the specified number. |
|---|
| && | Prints a single ampersand. |
|---|
| &"font name" | Prints the characters that follow in the specified font size.
Important:
In ASP, the font name must be within double quotation marks, as in the following example,
ws.PageSetUp.CenterHeader = "&""Tahoma""&Center Header" |
|---|
| &nn | Prints the characters that follow in the specified font size.
Use a two digit number to specify a size in points. |
|---|
| &N | Prints the total number of pages in the document. |
|---|
Formatting Headers and Footers in Microsoft Excel
When opening an existing Excel spreadsheet, ExcelWriter will preserve the header and
footer formatting created in Excel. However, if you change the header/footer text in
your ExcelWriter script and do not include Formatting Codes, the
formatting created in Excel will be overwritten, and default formatting applied. To preserve
header/footer formatting created in Excel, do not change header/footer text in script.
Alternatively, format headers and footers in script.
To format spreadsheet headers and footers in Excel and preserve the formatting
in ExcelWriter:
- In Microsoft Excel, open the File menu and select Page Setup...
- Select the Header/Footer tab.
- Select a header and/or footer from the drop-down list.
Or, create a custom header/footer.
- Save the spreadsheet.
- In an ExcelWriter script, open the spreadsheet containing headers/footers. Use
either ExcelTemplate or
ExcelApplication.Open to
open the spreadsheet.
- Modify the spreadsheet in script. Do not change the text of headers and footers; if you do,
the original formatting will be overwritten, and default formatting applied.
Copyright 2007 © SoftArtisans, Inc. All Rights Reserved.
|
