SoftArtisans OfficeWriter

The Range object represents a range of cells within a worksheet. A Range object may include non-adjacent areas. To create a Range object, use, Cells.Range, Cells.Rows, or Cells.Columns.

The property Cells.Range retrieves specified cells into a range object. By default, Cells.Range retrieves all cells in a Cells object. To specify a range of cells within the Cells object, set the Range property's four optional parameters. For example, to set a Range of from row 3 to row 4, and column 10 to column 14, use,

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

Example 1: Outlines

The following example uses GroupColumns and GroupRows to create outlines within a range.

The first row or column in an outline is the summary row or column, and is always visible. The rows or columns following the summary are the outline detail rows or columns, and will be visible or hidden, depending on the value of the Collapsed parameter. For definitions of GroupColumns/Rows parameters, see the following table.

<%@ Language=VBScript %>
<% Option Explicit %>
<!-- METADATA TYPE="TypeLib" UUID="{7BCD2133-64A0-4770-843C-090637114583}" -->
<%
Dim xlw, ws, rng

Set xlw = Server.CreateObject("Softartisans.Excelwriter") 
Set ws = xlw.worksheets(1)
	Set rng = ws.cells.range(1,1,20,10)

ws.cells("A2").value = "Product2"
ws.cells("A3").value = "   Product3"
ws.cells("A4").value = "  Product4"
ws.cells("A5").value = "  Product5"
ws.cells("A6").value = "  Product6"
ws.cells("A7").value = "  Product7"
ws.cells("A8").value = "   Product8"
ws.cells("A9").value = "  Product9"
ws.cells("A10").value = "  Product10"
ws.cells("A11").value = "  Product11"
ws.cells("A12").value = "  Product12"
ws.cells("A13").value = "  Product13"

ws.cells("A2").value = "Product14"
ws.cells("B3").value = "   Product15"
ws.cells("C4").value = "  Product16"
ws.cells("D5").value = "  Product17"
ws.cells("E6").value = "  Product18"
ws.cells("F7").value = "  Product19"
ws.cells("G8").value = "   Product20"
ws.cells("H9").value = "  Product21"
ws.cells("I10").value = "  Product22"
ws.cells("J11").value = "  Product23"
ws.cells("K12").value = "  Product24"
ws.cells("L13").value = "  Product25"

rng.GroupRows 4, 4, True
rng.GroupRows 9, 5, False
rng.GroupRows 3, 11, True
rng.GroupColumns 5, 3, False
rng.GroupColumns 4, 5, True
rng.GroupColumns 3, 7, False
rng.GroupColumns 2, 9, True
    
xlw.Save "Filename.xls", saOpenInPlace
response.end
set xlw = Nothing
%>

Example 2: Union, Intersection, Names, and Outlines

The following example imports data from the Northwind database to an Excel spreadsheet. The script sets five ranges, and names each using the Name property. The range names will be listed in Excel's drop-down name box above the top left corner of the spreadsheet. Select a range name from the list to highlight a range.

<%@ Language=VBScript %>
<% Option Explicit %>
<!-- METADATA TYPE="TypeLib" UUID="{7BCD2133-64A0-4770-843C-090637114583}" -->
<%
Dim filePath
Dim oConn, oRs, xlw, ws1, FirstRow, FirstCol
Dim AllRange, Range1, Range2, Range3, Range4, Range5, Range6
Dim DateStyle, ScriptName

Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open Application("connstring")
Set oRs = oConn.Execute ("select * from Orders where ShipRegion='AK' " & _
"or ShipRegion='WA' or ShipRegion='NM' order by ShipRegion" )

Set xlw = Server.CreateObject("Softartisans.ExcelWriter")

set ws1 = xlw.worksheets(1) 

'--- Convert Recordset output to workbook input
FirstRow = 3
FirstCol = 2

'--- Import the data in one step
'--- We add 1 to FirstRow to leave room for the headers
Set AllRange = ws1.cells.GetDataFromRecordset(oRs, True, FirstRow, FirstCol)

'--- Define Range1 beginning at row 4, column 2, and 
'--- spanning 4 rows and 5 columns. Name the range 
'--- "Range1." 
Set Range1 = ws1.cells.range(4,2,4,5) 
Range1.name = "Range1"

'--- Define Range2 beginning at row 6, column 3, and 
'--- spanning 7 rows and 9 columns. Name the range 
'--- "Range2." 
Set Range2 = ws1.cells.range(6,3,7,9)
Range2.name = "Range2"

'--- Define Range3 beginning at row 3, column 9, and 
'--- spanning 5 rows and 12 columns.  Name the range 
'--- "Range3." 
Set Range3 = ws1.cells.range(3,9,5,12)
Range3.name = "Range3"

'--- Expand Range1 by creating a union between Range1 
'--- and Range2.
Range1.union(Range2)

'--- Redefine Range2 as the intersection of Range2 and 
'--- Range3.
Range2.intersect(Range3)

'--- Define Range4 beginning at row 14, column 2, and 
'--- spanning 18 rows and 20 columns.  Name the range 
'--- "Range4." 
Set Range4 = ws1.cells.range(14,2,18,20)
Range4.name = "Range4"

'--- Outline rows 14 to 31, and hide the outline detail 
'--- rows by setting the Collapsed parameter to True.  
'--- Row 14 is the summary row and will be visible.  
Range4.grouprows 1,17,true

'--- Outline columns 9 to 12, and hide the outline detail 
'--- columns by setting the Collapsed parameter to True.  
'--- Column 9 is the summary column and will be visible.  
Range4.groupcolumns 9,2,true

'--- Outline columns 13 to 20, and hide the outline detail 
'--- columns by setting the Collapsed parameter to True.  
'--- Column 13 is the summary column and will be visible. 
Range4.groupcolumns 13,7,true

'--- Define Range5 beginning at row 33, column 2, and 
'--- spanning 19 rows and 20 columns.  Name the range 
'--- "Range5." 
Set Range5 = ws1.cells.range(32,2,19,20)
Range5.name = "Range5"

'--- Outline rows 33 to 50, and show the outline detail rows
'--- by setting the Collapsed parameter to False (which is 
'--- the default value).  Row 33 is the summary row.  Rows 34 
'--- to 50 are the outline detail rows.
Range5.grouprows 1,18,false

'--- Define a style that will format the cells into a date format.
'--- Apply the style on a range that references row 4 to 50 and 
'--- column 4 through 5(E,F and G).
Set DateStyle = xlw.CreateStyle
DateStyle.Number = 14 
DateStyle.WrapText = True

Set Range6 =  ws1.Cells.Range(4,5,46,3)
Range6.Style = DateStyle
Range6.ColumnWidth = 20

'--- Open the spreadsheet in the browser.
xlw.Save "Range1.xls", saOpenInPlace

'--- Error handling.
ScriptName = Request.ServerVariables("Script_Name") 
If Err.number <> 0 Then
  Response.Status = "500 Internal Server Error"
  Response.Write "<P><H2>Creation of Excel Spreadsheet Failed.<BR>" &_
  	 ScriptName & "</H2><BR>"
  Response.Write "Error : '" & Err.description & "'</P>"
Else
  Response.End
End IF

Response.end

Set xlw = Nothing
%>

Example 3: Use Range to Set PrintArea

The Range object can be used to assign a certain group of cells to ExcelWriter's PrintArea property. A PrintArea is a Range that is read by the PageSetup object when determining the portion of the page that should be printed.

The Range Object

What is a Range object?

Example 1: Outlines

Example 2: Union, Intersection, Names, and Outlines

Example 3: Use Range to Set PrintArea