|
|
The Worksheet Object (SAWorksheet)
The Worksheet object represents a single Excel worksheet. The first worksheet
in an Excel workbook is WorkSheets(1), the second, WorkSheets(2), and so on.
To create the first worksheet in a workbook, use,
Set ws = xlw.Worksheets(1)
Worksheet Methods and Properties
*This feature is not
available in ExcelWriterSE, ExcelWriterLE, or ExcelWriterFree.
| Worksheet Methods and Properties |
| Cells |
Cells represents the set of cells contained
in a single worksheet. Individual Cells can be addressed by Name
(for example, "B1") or by Number (for example, (1, 2)). References
by name are easier to read, and match the cell references in Microsoft
Excel. Accessing cells by number is significantly faster, and
it is easier to iterate over a set of cells by number than by
name. When referencing a cell by number, the format is cells(row,
column).
'--- Assign a value to a cell, addressing it by name
ws.cells("C7").value = "Total"
'--- Assign a value to a cell, addressing it by number
ws.cells(7, 3).value = "Total"
Top |
| Charts |
Returns a Charts
object, representing the set of all charts in a single worksheet.
To create a charts object, use,
Top |
| Copy* V4 |
The Copy method returns a new worksheet
that is a copy of the original worksheet. Note that this method does not copy
charts, images, or features that cannot be scripted by the ExcelApplication
object.
Example:
Top |
| FreezePanes* |
Freezes panes in a worksheet, allowing
you to scroll through one pane, while keeping other data visible.
FreezePanes takes a cell and splits the worksheet above the cell,
to the left of it, or both.
Examples:
-
'--- Freeze three panes.
'--- Horizontal split above row 20.
'--- Vertical split to the left of column H.
ws.FreezePanes(ws.cells("H20"))
-
'--- Freeze one pane.
'--- Split worksheet vertically,
'--- to the left of column H, into two panes.
ws.FreezePanes(ws.cells("H1"))
-
'--- Freeze one pane.
'--- Split worksheet horizontally,
'--- above row 20, into two panes.
ws.FreezePanes(ws.cells("A20"))
Top |
| Name |
Sets or retrieves the name of a worksheet.
Reference a worksheet by number, or by name. To reference a worksheet
by name, you must first assign a name to it, using the Name property.
The following example demonstrates naming a worksheet, and referencing
it by name.
Set ws = xlw.Worksheets(1)
ws.name = "FirstWS"
xlw.Worksheets("FirstWS").cells("E19").value = "Total"
Top |
| PageSetup* |
Returns a PageSetup
object, representing the layout properties of a printed spreadsheet.
To create a PageSetup object, use,
Set PageSetup = ws.PageSetup
Top |
| Pictures* |
Returns a Pictures
object, representing the set of all pictures in as single worksheet.
To create a Pictures object, use,
Set Pictures = ws.Pictures
Top |
| SelectSheet* |
Selects a worksheet to display when
the workbook is opened.
Example: The following will display the third worksheet
when the workbook is opened.
Set ws1 = xlw.Worksheets(1)
Set ws2 = xlw.Worksheets(2)
Set ws3 = xlw.Worksheets(3)
ws3.SelectSheet
Top |
| ShowGridlines* |
When set to True, the worksheet will
display gridlines. To hide gridlines, set ShowGridlines to False:
Top |
| ShowRowColHeaders* |
When set to True, the worksheet
will display row and column headers. To hide row and column headers,
set ShowRowColHeaders to False:
ws.ShowRowColHeaders = False
Top |
| Visible* |
You can set worksheets to be visible,
hidden, or very hidden with ExcelWriter. When Visibility is set
to true, Excel will display the worksheet, when set to false,
Excel will hide the worksheet, but users can use the Unhide command
to display the hidden worksheet. When set to "saxlSheetVeryHidden",
users will need to use Visual Basic for Applications to unhide
the hidden worksheet.
The Visible value names and codes are,
| Constant name |
VB Boolean Equivalent |
Integer value |
| saxlSheetVisible |
True |
-1 |
| saxlSheetHidden |
False |
0 |
| saxlSheetVeryHidden |
|
2 |
Please note: Excel will not open a workbook that has no visible
sheets.
Example: To set a worksheet to very hidden:
Set ws1 = xlw.Worksheets(1)
Set ws2 = xlw.Worksheets(2)
Set ws3 = xlw.Worksheets(3)
ws3.Visible = saxlSheetVeryHidden
|
| WriteProtect* |
The worksheet for which this property
is set will be write protected. This property is read/write. Users
will be restricted from altering the formatting or layout of the
worksheet, and they will be warned if they attempt to edit cell
content. Users can turn off worksheet protection only if they
supply the proper password in Excel. To set write protection for
a worksheet, set WriteProtect to an integer value. For example:
To remove write protection from a worksheet, set WriteProtect to
False:
Important notes:
- The integer value you set the WriteProtect property to is
not the password that you will use to unprotect a sheet
in Excel. The integer value corresponds to a plaintext string
password (for example, "-13228" is "pwd"). You must use the
SAExcelApplication.Open method on an existing protected sheet
to determine which integer value corresponds to your desired
password. This behavior is by design. For a detailed explanation
of this behavior, please see Features
In Depth: Protecting your Data.
- This functionality provides write protection, not encryption,
for a worksheet. Worksheet data will be fully readable when
opened, but will be uneditable within Excel. This property should
not be used to "protect" or "hide" sensitive
data.
Top |
| Zoom* |
Use the Zoom property to specify how large
or small you want your document to appear as a percentage of its
normal size. Zoom values are between 10 and 400; by default, Zoom
is set to 100.
Example: To make a worksheet appear 60% of its normal
size, use.
Top |
| *This feature is not
available in ExcelWriterSE, ExcelWriterLE, or ExcelWriterFree. |
Copyright © 2003, SoftArtisans, Inc.
|
|