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
| 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).
Signature:
[VBScript]
Property Cells As SACells (read-only)
Example:
[VBScript]
Dim XlwApp, WrkSht
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set WrkSht = XlwApp.Worksheets(1)
'--- Use the Cells collection to access individual cells
WrkSht.Cells(1,1).Value = "Cell A1"
WrkSht.Cells("A2").Value = "Cell A2"
Top |
Charts
Returns a Charts
object, representing the set of all charts in a single worksheet.
Signature:
[VBScript]
Property Charts As ICharts (read-only)
Example:
[VBScript]
Dim XlwApp, WrkSht
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set WrkSht = XlwApp.Worksheets(1)
//--- Use the Charts object to work with Excel charts
//--- Get a reference to the first chart in the worksheet
Set MyChart = WrkSht.Charts(1)
Top |
Copy
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.
Signature:
[VBScript]
Function Copy() As SAWorksheet
Example:
[VBScript]
Dim XlwApp, WrkSht, WrkShtCopy
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set WrkSht = XlwApp.Worksheets(1)
//--- Make a copy of the first worksheet with the Copy() method
Set WrkShtCopy = WrkSht.Copy()
Code sample:
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.
Signature:
[VBScript]
Sub FreezePanes(Cell As SACell)
Example:
[VBScript]
Dim XlwApp, WrkSht
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set WrkSht = XlwApp.Worksheets(1)
'--- Horizontal split above row 20.
'--- Vertical split to the left of column H.
WrkSht.FreezePanes WrkSht.Cells("H20")
'--- Split worksheet vertically,
'--- to the left of column H, into two panes.
WrkSht.FreezePanes WrkSht.Cells("H1")
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.
Signature:
[VBScript]
Property Name As String (read/write)
Example:
[VBScript]
Dim XlwApp, WrkSht
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set WrkSht = XlwApp.Worksheets(1)
'--- Name the first worksheet
WrkSht.Name = "First Worksheet"
Top |
PageSetup
Returns a PageSetup
object, representing the layout properties of a printed spreadsheet.
Signature:
[VBScript]
Property PageSetup As SAPageSetup
Example:
[VBScript]
Dim XlwApp, WrkSht, PgSetup
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set WrkSht = XlwApp.Worksheets(1)
'--- Get a reference to the SAPageSetup object for this worksheet
Set PgSetup = WrkSht.PageSetup
PgSetup.CenterHorizontally = True
Top |
Pictures
Returns a Pictures
object, representing the set of all pictures in as single worksheet.
Signature:
[VBScript]
Property Pictures As SAPictures
Example:
[VBScript]
Dim XlwApp, WrkSht, MyPic
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set WrkSht = XlwApp.Worksheets(1)
'--- Use the Pictures object to work with pictures on the worksheet
Set MyPic = WrkSht.Pictures.CreateFromFile2(1, 1, "c:\pics\myPicture.jpg")
Top |
PivotTables
Returns the collection of
PivotTables in a worksheet. A PivotTable is an
interactive table that combines and compares large amounts of data.
You can rotate its rows and columns to see different summaries of the source
data, and you can display the details of different fields.
Signature:
[VBScript]
Public ReadOnly Property PivotTables As IPivotTables
Example:
[VBScript]
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
'--- Open the workbook containing source data for the PivotTable.
XlwApp.Open "c:\reports\pivotdata.xls"
'--- Get a reference to the source data sheet.
Set wsData = XlwApp.Worksheets("DataSheet")
'--- Create a second worksheet and name it PivotSheet.
Set wsPivot = XlwApp.Worksheets(2)
wsPivot.Name = "PivotSheet"
'--- The data in the workbook is in a named range called
'--- DataNamedRange. Get an SARange object representing
'--- that range.
Set rngData = XlwApp.NamedRange("DataNamedRange")
'--- Pass CreatePivotTable the range of source data to
'--- create the PivotTable.
Set pvtTable = wsPivot.PivotTables.CreatePivotTable(rngData, 2, 2)
...
Top |
SelectSheet
Selects a worksheet to display when
the workbook is opened.
Signature:
[VBScript]
Sub SelectSheet()
Example:
[VBScript]
Dim XlwApp
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
'--- Select the third worksheet in the workbook
XlwApp.Worksheets(3).SelectSheet
Top |
ShowGridlines
When set to True, the worksheet will
display gridlines. To hide gridlines, set ShowGridlines to False.
Signature:
[VBScript]
Property ShowGridLines As Boolean
Example:
[VBScript]
Dim XlwApp, WrkSht
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set WrkSht = XlwApp.Worksheets(1)
'--- Turn off grid lines for the worksheet
WrkSht.ShowGridLines = 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:
Signature:
[VBScript]
Property ShowRowColHeaders As Boolean
Example:
[VBScript]
Dim XlwApp, WrkSht
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set WrkSht = XlwApp.Worksheets(1)
'--- Turn off row and column headers for the worksheet
WrkSht.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.
Signature:
[VBScript]
Property Visible As SAXlSheetVisibility (read/write)
Example:
[VBScript]
Dim XlwApp
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
'--- Hide the first worksheet
XlwApp.Worksheets(1).Visible = saxlSheetHidden
'--- Make the second sheet "very hidden"
XlwApp.Worksheets(2).Visible = saxlSheetVeryHidden
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.
|
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.
Signature:
[VBScript]
Property WriteProtect As Integer|Boolean (read/write)
Example:
[VBScript]
Dim XlwApp, WrkSht
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set WrkSht = XlwApp.Worksheets(1)
'--- Write protect the worksheet
WrkSht.WriteProtect = -13228
'--- Remove write protection by setting False
WrkSht.WriteProtect = False
- 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.
Signature:
[VBScript]
Property Zoom As Integer (read/write)
Example:
[VBScript]
Dim XlwApp, WrkSht
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set WrkSht = XlwApp.Worksheets(1)
'--- Set the worksheet zoom to 50% of its original size
WrkSht.Zoom = 50
Top |
Copyright 2006 © SoftArtisans, Inc. All Rights Reserved.
|
