Show code in...     

  

 
Object Model ExcelApplication Worksheets Worksheet Cells Cell Range
Charts Chart SeriesCollection Series 3DProperties Axis ChartFrame Line Area
PivotField PivotFields PivotTable PivotTables
Style Characters Font Pictures Picture DocumentProperties PageSetup


The Cells Object (SACells)

The Cells object represents the set of cells in a worksheet. To create a Cells object use, 

set xlw = Server.CreateObject("SoftArtisans.ExcelWriter")
set cells = xlw.worksheets(1).cells
Address an item of Cells (that is, a single cell), by name or by number.

Addressing Cells by Name Addressing Cells by Number
Format Cells("ColLetterRowNumber") Cells(row, column)
Example 
Cells("D8")
 
Cells(8, 4)
Advantages Easy to read, intuitive, matches MS Excel cell references Faster, easier to use when iterating over a set of cells

Cells Methods and Properties

Cells Methods and Properties
AddHyperlink*

Inserts a hyperlink in a specified cell. AddHyperlink takes six arguments: 



[VBScript]
Sub AddHyperlink( _
		FirstRow As Long, FirstCol As Long, NumRows As Long, _
		NumCols As Long, Address As String, [SubAddress As String])

To insert a hyperlink in a cell, or range of cells,

  1. Use AddHyperlink to insert a link in a specified cell, or range of cells. AddHyperlink assigns the
    upper left cell, the numbers of rows and columns the link will span, an address, and,
    optionally, a sub-address (e.g. "/search.htm"). The address may be http, https, ftp, mailto, or a UNC
    path.

  2. Merge the range of cells that will contain the link. This is not required. However, note that all cells
    in the range will function as a single link. Merging the cells in the range makes it clear that the range
    is a single unit.

  3. Assign a value to the cell specified by AddHyperlink. The cell value will function as the link.

  4. Apply the hyperlink style to the cell containing the link.

Note: Excel 95 does not support this feature.

Example: 


[VBScript]

Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set Cells = XlwApp.Worksheets(1).Cells

Cells.AddHyperlink 7,1,2,2,"http://www.softartisans.com"
Cells.MergeCells 7,1,2,2
Cells(7,1).Value = "SoftArtisans Home Page"
Cells(7,1).Style = XlwApp.style("Hyperlink")

Top
Cell

Use Cell to create a Cell object. A Cell object represents a single cell. The following example demonstrates setting a Cell object, and assigning a value to it.



[VBScript]
Property Cell(CellName As String) As SACell (read-only)

Top
Columns

Retrieves specified columns into a Range object. Columns takes two parameters, FirstCol and NumCols. NumCols is the number of columns to retrieve, including FirstCol. NumCols is optional and is set to 1 by default. The following example demonstrates retrieving 3 columns into a range object.



[VBScript]
Property Columns(FirstCol As Long, _
[NumCols As Long = 1]) As SARange (read-only)
	

Example:


[VBScript]

Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set Cells = XlwApp.Worksheets(1).Cells

'--- Get a range that holds columns D through F
Set ColRange = Cells.Columns(4, 3)

Top
ColumnWidth

Sets or returns the width of a specified column. ColumnWidth takes one parameter, the column index. 



[VBScript]
Property ColumnWidth(Column As Long) As Double (read/write)
	

Example:


[VBScript]

Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set Cells = XlwApp.Worksheets(1).Cells

'--- Set column width for the third column to 10
Cells.ColumnWidth(3) = 10

'--- Get the column width for the fifth column
ColWidth = Cells.ColumnWidth(5)

Top
CopyFromRecordset

Imports a database recordset into a worksheet, fills in headers automatically, and returns the total number of rows imported. CopyFromRecordset takes six parameters.



[VBScript]
Function CopyFromRecordset( _
	RecSet As RecordSet, [ShowFieldNames As Long = 1], _
	[FirstRow As Long = 1], [FirstCol As Long = 1], _
	[MaxRows As Long = 65536], [MaxColumns As Long = 256], _
	[FieldList As String], _
	[FieldListMode As SAXLFieldListMode = saxlsExclude]) As Long

ShowHeaders, FirstRow, FirstColumn, MaxRows, MaxColumns, FieldList, and FieldListInclude are optional. The following table lists their default values.

Optional ParameterDefault Value
ShowHeadersTrueRecordset column headers will be imported to the first row
FirstRow1The recordset will be imported into cell A1
FirstColumn1
MaxRows65536Excel 95 will delete rows after 16,384
MaxColumns256
FieldList "" All fields in the recordset are used
FieldListInclude saxlsExclude Excludes fields listed in FieldList parameter
Note: FieldList parameter is a comma delimited string specifying recordset fields to include or exclude. They may be referenced by field name or position, or any combination. The order fields are spcified in the FieldList parameter is the order in which they will return. 

 
Set oConn=  Server.CreateObject("ADODB.Connection") 
oConn.Open "DBQ="&filePath& ";Driver = {MicrosoftAccessDriver(*.mdb)};_
	DriverId = 25;FIL = MSAccess;" 
Set oRs = oConn.Execute("select * from qryOrders")

Set xlw = Server.CreateObject("Softartisans.ExcelWriter")
Set cells = xlw.Worksheets(1).Cells
'--- CopyFromRecordset "oRs", display recordset headers, 
'--- populate spreadsheet beginning with cell "A1" for a 
'--- maximum of 100 rows and 100 columns, include 
'--- only columns 1, 2, and 3 of the recordset.
NumRows = Cells.CopyFromRecordset(oRs, True, 1, 1, 100, 100, "1,2,3",_ 
				saxlsInclude)

xlw.Save "c:\workbook2.xls"
	
Set xlw = nothing

The following sample demonstrates importing a recordset into a spreadsheet using CopyFromRecordset.


[Run Sample] | [View Source]

Top
GetCellAt

Gets the value at a specified cell. GetCellAt takes two paramaters, row number and column number. 



[VBScript]
Function GetCellAt(Row As Long, Column As Long) As SACell


Example:


[VBScript]

Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set Cells = XlwApp.Worksheets(1).Cells

'--- Get cell B6
Set CellB6 = Cells.GetCellAt(6,2)

Top
GetDataFromExcelSheet*

Reads data from an existing spreadsheet. GetDataFromExcelSheet takes seven parameters, and returns a range object:



[VBScript]
Function GetDataFromExcelSheet(FileName As String, [Source As String], _
	[IncludeFormatting As Long = 1], [FirstRow As Long = 1], _
	[FirstCol As Long = 1], [MaxRows As Long = 65536], _
	[MaxCols As Long = 256]) As SARange

FileName is the name of the source file, that is, the spreadsheet from which ExcelWriter is reading. The optional parameter IncludeFormatting specifies whether to copy formatting from the source sheet. The optional parameters FirstRow, FirstCol, MaxRows, and MaxCols specify where to write the data in the destination spreadsheet.

The optional parameter Source specifies the first cell in the source range of cells. Source may include both sheet number and cell name (e.g. "Sheet2!$B$11"), or a cell name alone (e.g. "B11"). If Source does not include a sheet reference, ExcelWriter will read from the first sheet in the workbook. If you do not assign a Source value, ExcelWriter will start reading at cell A1 in the first worksheet.

Optional ParameterDefault Value
Source""
If you do not assign a Source value, ExcelWriter will start reading at cell A1 in the first worksheet.
IncludeFormatting1
Limitation: ExcelWriter will not include formatting in merged cells.
FirstRow1
FirstColumn1
MaxRows16384 in Excel 95 (BIFF7)
65536 in Excel 97/2000 (BIFF8)
MaxColumns256
(Limit imposed by Microsoft's BIFF8 format, not by ExcelWriter)		 

[VBScript]
Set xlw = Server.CreateObject("Softartisans.Excelwriter")
Set ws = xlw.Worksheets(1)
	
'--- Get data from workbook1.xls, sheet 1, starting at cell B3, 
'--- with formatting, and copy to cells B3:F12 in the destination 
'--- file.
Set ResultRange = 
ws.Cells.GetDataFromExcelSheet("C:\spreadsheets\workbook1.xls", "B3",_
				 True, 3, 2, 10, 5)
	
'--- Save the destination file
xlw.Save "c:\workbook2.xls"
	
Set xlw = nothing

Top
GetDataFromRecordset*

Imports a database recordset into a range of cells, fills in field names automatically, and returns the range object. GetDataFromRecordset takes six parameters.



[VBScript]
Function GetDataFromRecordset(RecordSet As RecordSet, _
[ShowFieldNames As Long = 1], _
[FirstRow As Long = 1], _
[FirstCol As Long = 1], _
[MaxRows As Long = 65536], _
[MaxColumns As Long = 256], _
[FieldList As String], _
[Mode As SAXLFieldListMode = saxlsExclude]) As SARange

ShowFieldNames, FirstRow, FirstColumn, MaxRows, MaxColumns, FieldList, and FieldListInclude are optional. The following table lists their default values.

Optional ParameterDefault Value
ShowFieldNamesTrueRecordset column headers will be imported to the first row
FirstRow1The recordset will be imported into cell A1
FirstColumn1
MaxRows65536Excel 95 will delete rows after 16,384
MaxColumns256
FieldList "" All fields in the recordset are used
FieldListInclude saxlsExclude Excludes fields listed in FieldList parameter
Note: FieldList parameter is a comma delimited string specifying recordset fields to include or exclude. They may be referenced by field name or position, or any combination. The order fields are spcified in the FieldList parameter is the order in which they will return. 

 
Set oConn=  Server.CreateObject("ADODB.Connection") 
oConn.Open "DBQ=   "&filePath& ";Driver = {MicrosoftAccessDriver(*.mdb)};_
			DriverId = 25;FIL = MSAccess;" 
Set oRs = oConn.Execute("select * from qryOrders")

Set xlw = Server.CreateObject("Softartisans.ExcelWriter")
Set cells = xlw.Worksheets(1).Cells
'--- GetDataFromRecordset "oRs", display recordset headers, 
'--- populate spreadsheet beginning  with cell "A1" for a 
'--- maximum of 100 rows and 100 columns, include only columns
'--- 1, 2, and 3 of the recordset.
NumRows = Cells.GetDataFromRecordset(oRs, True, 1, 1, 100, 100, "1,2,3",_
		 saxlsInclude)

xlw.Save "c:\workbook2.xls"
	
Set xlw = nothing

Top
GroupColumns*

Outlines specified columns within a range. GroupColumns takes three parameters:



[VBScript]
Sub GroupColumns(FirstColumn As Long, _
[NumColumns As Long = 1], _
[Collapsed As Boolean = False])
FirstColumn    

First column to be included in the outline. The first column can be designated as a summary column, (see the Cells.SummaryColumn property) and, if so, should be visible. Columns that follow or precede the summary column contain the outline detail and may be visible or hidden, depending on the value of the collapsed parameter. Column numbering is 1-based.

NumColumnsOptional.
Default value: 1.
Column numbering is 1-based.

CollapsedOptional.
Default value: False.
If set to True, the outline detail columns will be hidden. If set to False, the outline detail columns will be visible.

Important: If you wish to designate the location of the summary column as the last column of the outlined group, then the Cells.SummaryColumn property must be set using the saxlSummaryOnRight (0) value. The summary column should always be visible.

Example:

	

[VBScript]

'--- Outline columns A to D, and hide the outline detail columns.
'--- Column A is the summary column and will be visible. This 
'--- assumes that you have set the "SummaryColumn" property to 
'--- place summary columns to the left of detail columns. Columns 
'--- B to D are the outline detail columns and will be hidden.
cells.groupcolumns 1,4,true

See also, GroupRows.

Top
GroupRows*

Outlines specified rows within a set of cells. GroupRows takes three parameters:



 

[VBScript]
Sub GroupRows(FirstRow As Long, _
[NumRows As Long = 1], _
[Collapsed As Boolean = False])
FirstRow    

First row to be included in the outline. The first row can be designated as a summary row, (see the Cells.SummaryRow property) and, if so, should be visible. Rows that follow or precede the summary row contain the outline detail and may be visible or hidden, depending on the value of the collapsed parameter. Row numbering is 1-based.

NumRowsOptional.
Default value: 1.
Row numbering is 1-based.

CollapsedOptional.
Default value: False.
If set to True, the outline detail rows will be hidden. If set to False, the outline detail rows will be visible.

Important: If you wish to designate the location of the summary row as the last row of the outlined group, then the Cells.SummaryRow property must be set using the saxlSummaryBelow (0) value. The summary row should always be visible.

Example:


[VBScript]

'--- Outline rows 6 to 15, and hide the outline detail rows.
'--- Row 6 is the summary row and will be visible. This assumes 
'--- that you have set the "SummaryRow" property to place summary 
'--- row above the detail rows. Rows 7 and 15 are the outline 
'--- detail rows and will be hidden.
cells.grouprows 6,10,True

See also, GroupColumns.

Top
HPageBreak

Inserts a horizontal page break after a specified cell. 



[VBScript]
Sub HPageBreak(Cell As SACell)

Example:


[VBScript]

Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set Cells = XlwApp.Worksheets(1).Cells

'--- Create a horizontal page break after cell C1
Cells.HPageBreak Cells(1,3)

Top
Item

Represents a single cell in a Cells collection. Item is the default property of Cells, so Cells("A1") is equivalent to Cells.Item("A1").

                

[VBScript]
In VBScript, this is the default property for the Cells object.

Property Item(RowOrName, [Col As Long]) As SACell (read/write)

Example:


[VBScript]

Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set Cells = XlwApp.Worksheets(1).Cells

'--- Use the default Items property to access each cell object in the cells collection

'--- Get a reference to cell A1
Set CellA1 = Cells("A1") ' Cells(1,1) also works

'--- Set some properties for cell B2
Cells(2,2).Value = "This is cell B2"
Cells("B2").Format.Font.Bold = True

Top
MergeCells*

Combines a range of specified cells into a single cell. Use the address of the upper-left cell in the range to reference the merged cell. MergeCells takes four arguments: 

[VBScript]
Sub MergeCells(FirstRow As Long, FirstCol As Long, _
NumRows As Long, NumCols As Long)

Note: Excel 95 does not support this feature.

Example: 



[VBScript]

Set Xlwapp  = Server.CreateObject("SoftArtisans.ExcelWriter")
Set Cells = XlwApp.Worksheets(1).Cells

'--- Merge cells A10:C11
Cells.MergeCells 10, 1, 2, 3
Cells(10,1).Value = "Merged cells."

Top
PreserveStrings

Preserves all worksheet values as strings. PreserveStrings is set to False by default. When PreserveStrings is set to False, numeric cell entries are interpreted as numbers and right-aligned. If you set PreserveStrings to True, numbers are preserved as strings and, therefore, left-aligned. To set PreserveStrings to True, use,



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

Top
Range

Retrieves a range of cells into a Range object. By default, Range retrieves all cells in a Cells object. Range takes four optional parameters:

Optional ParameterDefault Value
FirstRow1
FirstCol1
NumRows65536ExcelWriter Limit imposed by Microsoft's BIFF8 format
 500ExcelWriterLE Edition Limit
NumCols256Limit imposed by Microsoft's BIFF8 format, not by ExcelWriter


[VBScript]
Property Range([FirstRow As Long = 1], _
[FirstCol As Long = 1], _
[NumRows As Long = 65536], _
[NumCols As Long = 256]) As SARange (read-only)

Example: 



[VBScript]

Set Xlwapp  = Server.CreateObject("SoftArtisans.ExcelWriter")
Set Cells = XlwApp.Worksheets(1).Cells

'--- Get a range of cells B5:E9
Set MyRange = Cells.Range(5,2,5,4)

Top
RowHeight

Sets or returns the height of a specified row. RowHeight takes one parameter, the row index. The following example demonstrates setting the RowHeight of row 1 to 20, and retrieving this value.



[VBScript]
Property RowHeight(Row As Long) As Double (read/write)

Example: 



[VBScript]

Set Xlwapp  = Server.CreateObject("SoftArtisans.ExcelWriter")
Set Cells = XlwApp.Worksheets(1).Cells

'--- Set row 6 to a height of 10
Cells.RowHeight(6) = 10

'--- Get the height of row 9
RowHeight = Cells.RowHeight(9)

Top
Rows

Retrieves specified rows into a Range object. Rows takes two parameters, FirstRow and NumRows. NumRows is the number of rows to retrieve, including FirstRow. NumRows is optional and is set to 1 by default.



[VBScript]
Property Rows(FirstRow As Long, [NumRows As Long = 1]) As SARange

Example: 



[VBScript]

Set Xlwapp  = Server.CreateObject("SoftArtisans.ExcelWriter")
Set Cells = XlwApp.Worksheets(1).Cells

'--- Get a range that holds rows 9-12
Set RowRange = Cells.Rows(9, 4)

Top
StandardHeight

Sets or returns the height of all rows where RowHeight was not modified. 



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

	
Example:
	


[VBScript]

Set Xlwapp  = Server.CreateObject("SoftArtisans.ExcelWriter")
Set Cells = XlwApp.Worksheets(1).Cells

'--- Set StandardHeight to 5
Cells.StandardHeignt = 5

'--- StandardHeight is a read/write property
'--- The value may also be read
Dim CellHeight
CellWidth = Cells.StandardHeight

Top
StandardWidth

Sets or returns the width of all columns where ColumnWidth was not modified. 



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

Example: 



[VBScript]

Set Xlwapp  = Server.CreateObject("SoftArtisans.ExcelWriter")
Set Cells = XlwApp.Worksheets(1).Cells

'--- Set StandardWidth to 20
Cells.StandardWidth = 20

'--- StandardWidth is a read/write property
'--- The value may also be read
Dim CellWidth
CellWidth = Cells.StandardWidth

Top
SummaryColumn*

Sets the location for a Summary Column in relation to the group of columns that comprise the detail block used in an outline. This property is used in association with the GroupColumns method that sets an outline level. It should be set prior to calling this method. Possible values are saxlSummaryOnLeft (-1), the default, or saxlSummaryOnRight (0). 



[VBScript]
Property SummaryColumn As SAXlSummaryColumn (read/write)

Example: 



[VBScript]

Set Xlwapp  = Server.CreateObject("SoftArtisans.ExcelWriter")
Set Cells = XlwApp.Worksheets(1).Cells

'--- Set SummaryColumn before calling GroupColumns
Cells.SummaryColumn = saxlSummaryOnRight

'--- Group colums A through D
Cells.GroupColumns 1, 4, True

Note: When designing a report that will contain summary columns, be sure to allow for an empty column to contain the summary column. When using the setting, saxlSummaryOnLeft (-1), the empty column must precede the detail columns, and when using saxlSummaryOnRight (0), it must follow the detail columns.

Top
SummaryRow*

Sets the location for a Summary Row in relation to the group of rows that comprise the detail block used in an outline. This property is used in association with the GroupRows method that sets an outline level. It should be set prior to calling this method. Possible values are saxlSummaryAbove (-1), the default, or saxlSummaryBelow (0). 



[VBScript]
Property SummaryRow As SAXlSummaryRow (read/write)

Example:


[VBScript]

Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set Cells = XlwApp.Worksheets(1).Cells

'--- Set SummaryRow before calling GroupRows
Cells.SummaryRow = saxlSummaryAbove

'--- Group rows 6 through 15
Cells.GroupRows 6, 10, True

Note: When designing a report that will contain summary rows, be sure to allow for an empty row to contain the summary row. When using the setting, saxlSummaryAbove (-1), the empty row must precede the detail rows, and when using saxlSummaryBelow (0), it must follow the detail rows.

Top
UsedRange V5

Signature: 


[VBScript]
Property UsedRange As SARange (read-only)

Example: 


[VBScript]

Dim XlwApp, WrkSht, MyRange, UsedRng
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set WrkSht = XlwApp.Worksheets(1)

'--- This range is A1:B6
Set MyRange = WrkSht.Cells.Range(1,1,6,2)

'--- Get the range of used cells
Set UsedRng = MyRange.UsedRange

Description

UsedRange returns a range of used cells within the specified range. If there are no use cells in the range, an empty range is returned.

UsedRange is a Read Only property.

Top
VPageBreak*

Inserts a vertical page break to the right of a specified cell. 



[VBScript]
Sub VPageBreak(Cell As SACell)

Example:


[VBScript]

Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set Cells = XlwApp.Worksheets(1).Cells

'--- Set a VPageBreak after cell J10
Cells.VPageBreak Cells(10,10)

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


Object Model ExcelApplication Worksheets Worksheet Cells Cell Range
Charts Chart SeriesCollection Series 3DProperties Axis ChartFrame Line Area
PivotField PivotFields PivotTable PivotTables
Style Characters Font Pictures Picture DocumentProperties PageSetup

Copyright © 2005, SoftArtisans, Inc.