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

AddHyperlink(FirstRow, FirstCol, NumRows, NumCols, Address, [SubAddress])
	

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:

Set Cells = ws.Cells
Cells.AddHyperlink 7,1,2,2,"http://www.yahoo.com"
Cells.MergeCells 7,1,2,2
Cells(7,1).Value = "Yahoo!"
Cells(7,1).Style = xlw.style("Hyperlink")
	

Top

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.

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

Top

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.

Dim Rng As SARange
Set Rng = Application.Wks(1).Cells.Columns(3, 3) 
	

Top

Sets or returns the width of a specified column. ColumnWidth takes one parameter, the column index. The following example demonstrates setting the ColumnWidth of column 1 (A) to 20, and retrieving this value.

Cells.ColumnWidth(1) = 20
MyWidth = Cells.ColumnWidth(1)
	

Top

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

NumRows = cells.CopyFromRecordset(ADO-RecordSet, [ShowHeaders (True/False)] 
	[FirstRow], [FirstColumn], [MaxRows], [MaxColumns], [FieldList], 
	[FieldListInclude (saxlsExclude/saxlsInclude)])

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

 
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.

Top

Gets the value at a specified cell. GetCellAt takes two paramaters, row number and column number. The following example demonstrates getting the value at cell(26,2), and displaying it in cell A1.

cells("A31").value = cells.getcellat(26,2)
	

Top

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

Range = cells.GetDataFromExcelSheet(FileName, [Source], [IncludeFormatting]
		[FirstRow], [FirstCol], [MaxRows], [MaxCols])
	

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.

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

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

Range = cells.GetDataFromRecordset(ADO-RecordSet, 
	[ShowFieldNames (True/False)], [FirstRow],[FirstColumn],
	[MaxRows], [MaxColumns], [FieldList], 
	[FieldListInclude (saxlsExclude/saxlsInclude)])
	

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

 
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

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

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:

	
'--- 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

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

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:

'--- 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

Inserts a horizontal page break after a specified cell. The following example demonstrates inserting a page break after cell A15.

cells.hpagebreak(ws.cells("A15"))
	

Top

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

Top

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:

	MergeCells(FirstRow,FirstCol,NumRows,NumCols)

Note: Excel 95 does not support this feature.

Example:

Cells.MergeCells 10,1,2,3
Cells(10,1).Value =  "Merged cells"
	

Top

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,

Cells.PreserveStrings = True
	

Top

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

The following example demonstrates retrieving the range of cells from row 3 to row 4, and column 10 to column 14.

set Rng = Cells.Range(3,10,2,5)
	

Top

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.

Cells.RowHeight(1) = 20
MyHeight = Cells.RowHeight(1)
	

Top

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. The following example demonstrates retrieving 3 rows into a range object.

Dim Rng As SARange
Set Rng = Application.Wks(1).Cells.Rows(3, 3) '--- Gets rows 3,4, and 5
	

Top

Sets or returns the height of all rows where RowHeight was not modified. The following example demonstrates setting StandardHeight to 20, and retrieving this value.

Cells.StandardHeight = 20
StdWidth = Cells.StandardWidth
	

Top

Sets or returns the width of all columns where ColumnWidth was not modified. The following example demonstrates setting StandardWidth to 20, and retrieving this value.

Cells.StandardWidth = 20
StdWidth = Cells.StandardWidth
	

Top

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).

'--- Set the summary column to appear following the detail block of columns
Cells.SummaryColumn = saxlSummaryOnRight	
	
'--- Reads the preset location of summary columns
SummaryColumnLocation = Cells.SummaryColumn
	

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

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).

'--- Set the summary row to appear following the detail block of rows
Cells.SummaryRow = saxlSummaryBelow	
	
'--- Reads the preset location of summary rows
SummaryRowLocation = Cells.SummaryRow
	

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

Inserts a vertical page break to the right of a specified cell. The following example demonstrates inserting a page break after cell J1.

ws.cells.vpagebreak(ws.cells("J1"))
	

Top