Forums     Knowledge Base     OfficeWriter Online     
 
 
This documentation is for
OfficeWriter v3.7.1
ASP/COM Platform
View Docs for Another
Version or Platform

Programmer's Reference > ExcelApplication > Objects > Cell
ExcelApplication is not
available in ExcelWriterSE.

The Cell Object (SACell)

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

set cellB26 = oWorksheet.cells("B26")
cellB26.value = "Product"

Cell Methods and Properties

Cell Methods and Properties

Characters

Returns a Characters object which represents a range of characters within a cell. Characters takes two parameters:

StartIndex The character position within the specified cell at which to begin the Characters object.
Length Number of characters and spaces to include in the Characters object.
	

[VBScript]
Property Characters(StartIndex As Long, Length As Long) As ICharacters (read/write)

Examples: 


[VBScript]
Set xlw = Server.CreateObject("Softartisans.excelwriter") 
...
set ws = xlw.worksheets(1) 
strHeader = "Rich Character Formatting Sample"
ws.Cells(3,3).Value = strHeader
set oCharacters = ws.Cells(3,3).Characters(1,Len(strHeader))
oCharacters.Font.Bold = True
oCharacters.Font.Size = 14
...

Top

ClearContents

The ClearContents method deletes any value or formula within the specified cell. 

	

[VBScript]
Sub ClearContents()

Example: 

[VBScript]

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

'--- Clear the contents from cell A1
WrkSht.Cells(1,1).ClearContents

Top

ClearFormats

Clears all formatting in the specified cell.

	

[VBScript]
Sub ClearFormats()

Example: 


[VBScript]
Dim XlwApp, WrkSht
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set WrkSht = XlwApp.Worksheets(1)
...
'--- Clear the formats from cell A1
WrkSht.Cells(1,1).ClearFormats

Top

Col

Returns the column number of a specified cell. The following example demonstrates setting a cell object, and retrieving its column number.

	

[VBScript]
Property Col As Integer (read-only)

Example: 


[VBScript]

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

'--- Get the column number of a cell
ColNum = WrkSht.Cells("A1").Col

Top

Format

Use Format to create a look for a single cell. Both Format and Style define the appearance of cells. However, Style is used to define the look of a set of cells, while Format defines the look of an individual cell.

Define a format for a single cell, rather than create a style, only if it is unlikely that you will apply the same format to other cells. Whenever you define a format for an individual cell, you add format information to the spreadsheet, so if you define the same format repeatedly, the resulting spreadsheet file will be unnecessarily large. When you apply a single Style object to several cells, the style information is written to the spreadsheet only once, conserving space.

The properties of Format are, Font, Hidden, HorizontalAlignment, Locked, Number, Orientation, VerticalAlignment, and WrapText. For definitions of each of these properties, see Style. The following example demonstrates defining a format for a cell.

	

[VBScript]
Property Format As SAFormat (read/write)

Example: 


[VBScript]

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

'--- Make a cell bold-faced with the Format property
WrkSht.Cells(1,1).Format.Font.Bold = Trye

Top

Formula

Use Formula to add Microsoft Excel formulas to your spreadsheet. For a complete list of available formulas see Formula Functions.

When applying a formula to a cell, always use an equal sign (=), as you would when creating an Excel spreadsheet. The following example demonstrates using a formula.

	

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

Example: 


[VBScript]

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

'--- Add a formula to a cell with the Formula property
WrkSht.Cells(1,1).Value = 1
WrkSht.Cells(2,1).Value = 2
WrkSht.Cells(3,1).Formula = "=SUM(A1:A2)"

Top

Name

Returns the specified cell's name. A cell name includes its column letter and row number, for example B5. To retrieve the name of a cell, use,

	

[VBScript]
Property Name As String (read-only)

Example: 


[VBScript]

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

'--- The Name property returns the Excel address for a cell
CellName = WrkSht.Cells(10,10).Name '--- returns "J10"

Top

PutValue

Puts a value in a cell. 

		

[VBScript]
Sub PutValue(newVal As Variant)
is equivalent to, 
	

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

Example: 


[VBScript]

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

'--- Insert a value into the cell
WrkSht.Cells(1,1).PutValue "Some value."

Top

Row

Return the row number of a specified cell. The following example demonstrates setting a cell object, and retrieving its row number.

	


[VBScript]
Property Row As Integer (read-only)

Example: 



[VBScript]

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

'--- Get the row number of a cell
RowNum = WrkSht.Cells("A1").Row

Top

SetAddInFormula

SetAddInFormula allows you to use custom formulas from Excel add-in files. SetAddInFormula takes two parameters:

FileName The name of the file that contains the add-in formula. This parameter can be:
  • A complete local or UNC path
  • The current directory of the generated spreadsheet
  • A sub-directory of the generated spreadsheet's directory
  • Up a directory from the current directory of the generated spreadsheet
  • Excel's Add-In Library directory or sub-directory
The path specified by FileName is a client path. The path can include forward and/or backward spashes.
FormulaVal The formula containing the custom function. 
	
[VBScript]
Sub SetAddInFormula(FileName As String, FormulaVal As String)

Examples:

Pass SetAddInFormula a complete local path:

cell.SetAddInFormula("E:\directory\AddInFile.xla",_
	"=FuncName(A1,A2:A7,...)")

Pass SetAddInFormula a complete UNC path: 

cell.SetAddInFormula("\\drive\directory\AddInFile.xla",_
	"=FuncName(A1,A2:A7,...)")

Pass SetAddInFormula the current directory of the spreadsheet or a sub-directory (with or without a beginning dot): 

cell.SetAddInFormula(".\FileInLibraryDir.xla",_
	"=FuncName(A1,A2:A7,...)")

cell.SetAddInFormula("\Dir1\Dir2\FileInLibraryDir.xla",_
	"=FuncName(A1,A2:A7,...)")

Pass SetAddInFormula the directory above the current directory:

cell.SetAddInFormula("..\FileInLibraryDir.xla",_
	"=FuncName(A1,A2:A7,...)")

Pass SetAddInFormula Excel's Add-In Library directory or sub-directory (start with the name of the file or the sub directory): 

cell.SetAddInFormula("FileInLibraryDir.xla",_
	"=FuncName(A1,A2:A7,...)")

cell.SetAddInFormula("SubDir\FileInLibraryDir.xla",_
	"=FuncName(A1,A2:A7,...)")

Top

Style

Use Style to apply a defined Style object to a cell. Both Format and Style define the look of cells, but a format is created for a single cell, while a style is defined for reuse.

Define a format for a single cell, rather than create a style, only if it is unlikely that you will apply the same format to other cells. Whenever you define a format for an individual cell, you add format information to the spreadsheet, so if you define the same format repeatedly, the resulting spreadsheet file will be unnecessarily large. When you apply a single Style object to several cells, the style information is written to the spreadsheet only once, conserving space.

To define a style, first set a Style object, and assign values to any or all of its properties, for example,

	

[VBScript]
Property Style As SAStyle (read/write)

Example: 


[VBScript]

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

'--- Create and configure a SAStyle object
Set MyStyle = XlwApp.CreateStyle()
MyStyle.Font.Name = "Arial"
MyStyle.Font.Bold = True
MyStyle.HorizontalAlignment = sahaRight

'--- Apply the style to a cell
WrkSht.Cells(1,1).Style = MyStyle

Top

Value

Use Value to assign a value to a cell.

	

[VBScript]
Property Value As Variant (read/write)
is equivalent to, 
	

[VBScript]
Sub PutValue(newVal As Variant, [PropertyType As Integer])

Example: 




[VBScript]

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

'--- Put a value into the cell
WrkSht.Cells(1,1).Value = "Some value."

'--- Read a value from a cell
CellValue = WrkSht.Cells(1,1).Value

Top
This feature is not available in ExcelWriterSE.




Copyright 2006 © SoftArtisans, Inc. All Rights Reserved.